VBA Delete rows in table that are < Date - 7

[JulianvO]

Hi

Table1 contains 6 columns of which column 6 is in a short date format.

The date in column 6 must be automatically deleted if less than 7 days from the current date or a date in another cell - control cell.
The code below gives error 424 Object required. The code runs from the workbook.

Option Explicit

Sub DeleteRows()

Dim LastRow As Range
Dim i As Integer

Set LastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

For i = LastRow To 2 Step -1

If ThisWorkbook.Worksheets("Sheet1").Cells(i, 6).Value < Date Then

Rows(i).Delete

End If
Next

ThisWorkbook.Worksheets("Sheet1").Cells(i, 1).Select

End Sub

Can someone kindly assist me?

 

[MARK858]

LastRow is a Long not a Range, also should not have the word Set in front of the line because it isn't a Range/Object

 

[JulianvO]

Hi Mark858

Thanks for the assistance. Works well, but deletes all the rows above the table, but one. Table is located at A11.

Any further advice?

Thanks again

 

[MARK858]

Your step to 2 change to 12

 

[JEC]

You could just use autofilter

Sub jec()
 Application.ScreenUpdating = False
 With Range("A11").CurrentRegion
   .AutoFilter 6, "<" & CLng(Date - 7)
   .Offset(1).EntireRow.Delete
   .AutoFilter
 End With
End Sub

It is even better when you refer to a real tablename

Sub jecc()
 Application.ScreenUpdating = False
 With Sheets(1).ListObjects("Table1").Range
   .AutoFilter 1, "<" & CLng(Date - 7)
   .Offset(1).EntireRow.Delete
   .AutoFilter
 End With
End Sub

 

[JulianvO]

Your step to 2 change to 12

Hi Mark858

Thanks. That did it!

 

[JulianvO]

You could just use autofilter

Sub jec()
 Application.ScreenUpdating = False
 With Range("A11").CurrentRegion
   .AutoFilter 6, "<" & CLng(Date - 7)
   .Offset(1).EntireRow.Delete
   .AutoFilter
 End With
End Sub

It is even better when you refer to a real tablename

Sub jecc()
 Application.ScreenUpdating = False
 With Sheets(1).ListObjects("Table1").Range
   .AutoFilter 1, "<" & CLng(Date - 7)
   .Offset(1).EntireRow.Delete
   .AutoFilter
 End With
End Sub

Hi JEC

Thanks. Will keep your suggestions.


They will come in handy!

 

[MARK858]

Hi Mark858

Thanks. That did it!

You're welcome, personally I would go with the autofilter method, just with the exception that you don't need the offset if you use DataBodyRange rather than Range

Sub RemoveDates()
    With Sheets(1).ListObjects("Table1").DataBodyRange
        .AutoFilter 1, "<" & CLng(Date - 7)
        .EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[JEC]

Correct