have macro which delete row based on if not todays date get delete method range class failed

[SWAY14]

Hello,
So I have a macro which deletes the entire row if the date is less than current date. it works fine but the thing is on windows machine I get "delete method of range class failed" since if there is cells with that do not have todays date. But I need it to get out of the loop if this is the case not throw an error. on Mac I do not have this issue.

My code
Sub STEP2()
Application.DisplayAlerts = False
Dim cell As Range
For Each cell In Worksheets("Sheet2").Range("A1:A1000")
If cell.Value < Date Then
cell.EntireRow.Delete
End If
Next cell

End Sub
Any help with be grateful

 

[vcoolio]

Hello Sway14,

I'd be surprised if that type of loop construct would work correctly, even with a Mac. If you're deleting rows using a loop construct, then you need to work from the bottom up, as follows:-

Option Explicit
Sub STEP2()

Application.ScreenUpdating = False
       
          Dim i As Long, lr As Long, ws2 As Worksheet
          Set ws2 = Sheets("Sheet2")
          lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
         
          For i = lr To 2 Step -1
                      If ws2.Cells(i, 1).Value < Date Then
                      ws2.Cells(i, 1).EntireRow.Delete
                End If
          Next i

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[SWAY14]

Hello Sway14,

I'd be surprised if that type of loop construct would work correctly, even with a Mac. If you're deleting rows using a loop construct, then you need to work from the bottom up, as follows:-

Option Explicit
Sub STEP2()

Application.ScreenUpdating = False
      
          Dim i As Long, lr As Long, ws2 As Worksheet
          Set ws2 = Sheets("Sheet2")
          lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
        
          For i = lr To 2 Step -1
                      If ws2.Cells(i, 1).Value < Date Then
                      ws2.Cells(i, 1).EntireRow.Delete
                End If
          Next i

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Thank you very much for the feedback and for the great help this worked amazing.

 

[vcoolio]

You're welcome Sway. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.