Excel vba for loop

Question

Sub letr()

Dim i As Integer

For i = 3 To 25

    If Cells(i, 7) <> "CA" Then
    Rows(i).Delete
    End If

Next i

End Sub

The above code does not delete all the results(Total 4). i have to run the code twice to delete all the 4 results. Can anyone show me what i did wrong?

This is such a reoccuring problem. Run deletion from bottum-up or you simply not iterating all rows. To see this happening, step through your code using `F8` and see results per step. Then again, best is to not delete one-by-one but in one go after you used `Union`. Here is an [example](https://stackoverflow.com/a/59975507/9758194). Let me know if that answers your question so we can close this thread. — JvdV, Jun 19 '20 at 20:04
If you delete from top to bottom, you are effectively moving rows up while your iterator i is still stepping forwards. This means that if you had 3:NY 4:NY 5:CA then at i = 3, the first NY would be deleted. now you have 3:NY 4:CA left but your iterator has advanced and i = 4. Since you deleted row 3, the new row 3 still holds the value NY in this case, but your loop has moved on to examine row 4. — eNc, Jun 19 '20 at 20:08

score 1 · Answer 1 · answered Jun 19 '20 at 20:16

1

As others have said you need to go from the bottom of your data to the top, if you are only interested in rows 3 to 25 try:

For i = 25 To 3 Step -1

answered Jun 19 '20 at 20:16

Archie Adams

779
5
19

score 0 · Answer 2 · answered Jun 19 '20 at 20:21

It's possible with an increasing index, with a While loop.

When you don't delete a row, simply increment the index to test the next one.

When you delete a row, don't advance as the next row is moved to the current row. Instead, reduce the bound by one.

Dim I As Integer, N As Integer
I = 3
N = 25
While I <= N
    If Cells(I, 7) <> "CA" Then
        Rows(I).Delete
        N = N - 1
    Else
        I = I + 1
    End If
Wend

eNc · Answer 3 · 2020-06-19T20:37:30.697

0

If you still want to go top-down and you know the start and end row you could use a while loop.

Dim i As Integer: i = 3
Dim c As Integer: c = 1

Do While c < (25 - 3 + 2)
    If Cells(i, 7).Value <> "CA" Then
        Rows(i).Delete
    Else
        i = i + 1
    End If
    c = c + 1
Loop

Or using a for loop

For c = 3 To 25
    If Cells(i, 7).Value <> "CA" Then
        Rows(i).Delete
    Else
        i = i + 1
    End If
Next c

edited Jun 19 '20 at 20:37

answered Jun 19 '20 at 20:24

eNc

1,021
10
23

Interesting. With this approach, it's actually simpler to replace the `Do While` with a `For` loop, as the index `c` always increases. – Jun 19 '20 at 20:31
@Jean-ClaudeArbaut yep, using a for loop to iterate c would also be syntactically cleaner too. I will add to my ans. Thx! – eNc Jun 19 '20 at 20:34

Excel vba for loop

3 Answers3