0

I have a loop that when it runs, will delete the cells in column K that have the text "No AF". Here is my code:

  If .Range("B7") = "N" Then
        For i = 1 To 100

            If (.Range("K" & i).Value) Like "*No AF*" Then
                .Range("K" & i).Delete

            End If

        Next
    End If

When I run the code, it does delete all cells but one that contain the text "No AF". I then have to run the code a second time to get rid of the last cell that contains "No AF". Why do I have to run the code twice and how can I fix this.

Thanks,

GCC

Mesut Akcan
  • 899
  • 7
  • 19
GCC
  • 285
  • 6
  • 23
  • 5
    When deleting rows you need to loop backwards: `For i = 100 to 1 Step -1`. – dwirony Dec 10 '18 at 21:55
  • This worked. Why do I have to delete looping backwards? – GCC Dec 10 '18 at 21:58
  • 6
    Because when you delete row 1, row 2 becomes the new row 1 before you finish your loop. So you essentially skip every other row in your loop. When you get to the next iteration, which would be `i = 2`, you are actually on the old 3rd row before row 1 was deleted. – K.Dᴀᴠɪs Dec 10 '18 at 21:58
  • Thanks so much guys – GCC Dec 10 '18 at 21:59
  • 3
    Alternatively you can build a `Range` object with `Union`, select all the cells you want to delete, and delete them in 1 single operation. – Mathieu Guindon Dec 10 '18 at 22:13

0 Answers0