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,
if you want to loop and delete its best to mark the rows first and delete them at once or use an array.
lastr = Range("a2").End(xlDown).Row
dim DR() as long
dim c as long
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
c = c +1
redim preserve DR(c)
dr(c-1) = R
End If
Next r
'delete the rows one by one, or you could build a string and delete once.
For r = 0 to UBound(DR)
Rows(dr(i).delete ' or entirerow delete
next i
You are subtracting 1 from the loop variable, so it loops forever.
In Visual Basic for loops, "from" and "to" are calculated once at the beginning (they are fixed), but the loop variable is increased each time. So
For r = fromExp to toExp
SomeCode()
End For
behaves the same as
Dim f = fromExp
Dim t = toExp
r = f
While (r < t)
SomeCode()
r = r + 1
End While
In your example, the code changes toExp
For r = fromExp to toExp
toExp = toExp + 1
r = r - 1
EndFor
but that doesn't affect the loop:
Dim f = fromExp
Dim t = toExp
r = f
While (r < t)
toExp = toExp + 1 // does not affect the loop
r = r - 1
r = r + 1 // r is unchanged
End While
The loop variable is unchanged, so it loops forever.
Best practice: do not alter the loop variable within a For loop.
Instead of using the variable for the loop (r), create a your own variable to track your position and update.
lastr = Range("a2").End(xlDown).Row
CurrentPosition = 2
For r = 2 To lastr
If Cells(CurrentPosition, 1).Value <> "SHORT POSITIONS" And Cells(CurrentPosition, 7).Value = 0 And Cells(CurrentPosition,10).Value <> "Yes" Then
Rows(CurrentPosition).Delete
CurrentPosition = CurrentPosition - 1
End If
CurrentPosition = CurrentPosition + 1
Next r
For r = lastr to 2 step -1. This way, you don't have to bother withr = r-1andlastr = lastr -1? lastrin the immediate window...