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?
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
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
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
F8and see results per step. Then again, best is to not delete one-by-one but in one go after you usedUnion. Here is an example. Let me know if that answers your question so we can close this thread.