Solved: for loop

[chungtinhlak]

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.

[vba]
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[/vba]

[mikerickson]

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.
[VBA]For i = usedcell to 2 step -1[/vba]

[chungtinhlak]

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]

I get it now.