[SOLVED] VBA For loop not exiting

VBA For loop not exiting

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

Solution

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,