Loop through cells not completing properly

[sharky12345]

I'm using this to loop through column A and identify any cell which is less than 30 days ago, (the cells contain dates);

MaxDate = Date - 30

LastRow = Sheet5.Range("A65000").End(xlUp).row
For Each Cell In Sheet5.Range("A2:A" & LastRow)

If Cell.Value >= MaxDate Then

Cell.EntireRow.Delete

End If
Next

It's working, sort of, but doesn't do every cell, I have to run it several times to get it to do every cell - for info there are about 10000 rows of data but not sure if that would make a difference?

 

[Fluff]

How about

Dim Rng As Range
MaxDate = Date - 30

LastRow = Sheet5.Range("A65000").End(xlUp).Row
For Each cell In Sheet5.Range("A2:A" & LastRow)
   
   If cell.Value >= MaxDate Then
      If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell)
   End If
Next
If Not Rng Is Nothing Then Rng.EntireRow.Delete

 

[Joe4]

That is because when you are deleting or inserting rows within a loop, you need to work in reverse order (start from the bottom and go up). Otherwise, the range is constantly shifting as you are trying to loop through it and can cause rows to get missed (i.e. if you rows 4 and 5 need to be deleted, and you are currently on row 4, when you delete row 4, the old row 5 becomes the new row 4 -- however the next row in the loop is 5, so the "new" row 4 gets missed).

Try this:

Application.ScreenUpdating = False

MaxDate = Date - 30

LastRow = Sheet5.Range("A65000").End(xlUp).row

For r = LastRow to 2 Step -1
    If Sheet5.Cells(r,"A") >= MaxDate Then
        Sheet5.Rows(r).Delete
    End If
Next r

Application.ScreenUpdating = True

 

[sharky12345]

Fluff, as usual you come to the rescue - that works just fine.

Thanks for this one and every other little conundrum I've thrown at you this year.

Happy New Year!

 

[sharky12345]

That is because when you are deleting or inserting rows within a loop, you need to work in reverse order (start from the bottom and go up). Otherwise, the range is constantly shifting as you are trying to loop through it and can cause rows to get missed (i.e. if you rows 4 and 5 need to be deleted, and you are currently on row 4, when you delete row 4, the old row 5 becomes the new row 4 -- however the next row in the loop is 5, so the "new" row 4 gets missed).

Try this:

Application.ScreenUpdating = False

MaxDate = Date - 30

LastRow = Sheet5.Range("A65000").End(xlUp).row

For r = LastRow to 2 Step -1
    If Sheet5.Cells(r,"A") >= MaxDate Then
        Sheet5.Rows(r).Delete
    End If
Next r

Application.ScreenUpdating = True

Got it, and understand now - thanks Joe!

 

[Joe4]

You are welcome.
Glad we were able to help you!