For each skipping rows

[Luke777]

Hi all,

I'm using the following code to delete rows.

For Each cell in sTasks.Range("C3:C" & sRange)
If cell.value = "Completed" then cell.entirerow.delete
next

In practice the range is something like C3:C50, but my problem is that only every other row is being delete. I understand why, because the row gets delete, next row moves up so 15 becomes 14 and gets skipped assuming it is also "complete".

So I understand what's going on, but I don't know what the best way to fix it is... maybe store the ranges to be deleted and do them all at once? or is there a way of telling the For Each to go back a step? Like the opposite of Next?

Thanks

 

[Joe4]

The best way to do this is to loop through the rows backwards. You can do that like this:

Dim lr as Long, r as Long

'Find last row in column C with data
lr = sTasks.Cells(sTasks.Rows.Count, "C").End(xlUp).Row

'Loop through rows backwards
For r = lr to 3 Step -1
   If sTasks.Cells(r, "C").Value = "Completed" then sTasks.Cells(r, "C").EntireRow.Delete
next