PDA

View Full Version : Solved: for loop



chungtinhlak
01-14-2009, 05:55 PM
Column O contains 19 rows with 1 on it, there for, it should delete 19, but it always delete 18, i have to run the code the second time for it to delete that last one. why? the last one is located at row 519.


Dim usedcell as long
'usedcell is over 4000.

Range("O1").Select
For i = 2 To usedcell
If Range("O" & i).Value = "1" Then
Range(i & ":" & i).Delete Shift:=xlUp
x = x + 1
End If
Next i

mikerickson
01-14-2009, 06:06 PM
You are working from top to bottom.
If O3 = 1 and you delete it (when i = 3) what was O4 becomes O3.
The next loop i=4 looks at the new O4 (previously O5) and the new O3 is un-inspected and un-deleted.

The fix is that when deleting cells, one works from the bottom up, so that a deletion does not disturb any unexamined cells.
For i = usedcell to 2 step -1

chungtinhlak
01-14-2009, 06:13 PM
If O3 = 1 and you delete it (when i = 3) what was O4 becomes O3.

I is ahead by 1, I don't quite get it, when i = 3, then O & i = O3 >>> delete I:I >>> 3:3.

right?

chungtinhlak
01-14-2009, 06:15 PM
I get it now.