I am looping through rows of a table and deleting rows if certain conditions are not met. For some reason my for loop never exits even when its done. What am I doing wrong?
lastr = Range("a2").End(xlDown).Row
For r = 2 To lastr
If Cells(r, 1).Value <> "SHORT POSITIONS" And Cells(r, 7).Value = 0 And Cells(r, 10).Value <> "Yes" Then
Rows(r).Delete
r = r - 1
lastr = lastr - 1
End If
Next r
ALWAYS start at the bottom and work towards the top when deleting rows. Failing to work from the bottom to the top will result in skipped rows as the position of the rows are reset after the row deletion.
NEVER reset your counter in a For ... Next Statement. Changing r
mucks things up. Changing lastr
has no effect. It will still go to the lastr
that was the original value when you entered the loop.
lastr = Range("a" & ROWS.COUNT).End(xlUP).Row
For r = lastr To 2 STEP -1 '<~~ VERY IMPORTANT
If Cells(r, 1).Value <> "SHORT POSITIONS" And Cells(r, 7).Value = 0 And Cells(r, 10).Value <> "Yes" Then
Rows(r).Delete
End If
Next r
TYPICALLY, it is better to look for the last populated cell from the bottom up,