How to delete rows in a loop?

Question

I am trying to delete rows with specific string inside cell value.

For i = 1 to 5000
    If InStr(cells(i,1).value, "GUACAMOLE", vbTextCompare) Then
        Rows(i).EntireRow.Delete    
    End If
Next I

It deletes only a few rows that contain Guacamole string, the range of loop is proper. I tried a lot of methods, like using left, or mid or just by single symbols.

Others and I have covered this many times in the past. Looping and deleting is highly inefficient. Either use `Union` and then delete in the end in one go or use `Autofilter` to delete without looping. Do a search in stackoverflow. You will get many links (some by me and some by others) — Siddharth Rout, Aug 22 '19 at 07:09
Here is one such [LINK](https://stackoverflow.com/questions/11317172/delete-row-based-on-condition) And here is the [Union Method](https://stackoverflow.com/questions/9644453/how-to-look-for-repeated-rows-and-then-delete-one-of-them-requires-vba). If you do a search, you will find many more — Siddharth Rout, Aug 22 '19 at 07:15

braX · Answer 1 · 2019-08-22T07:21:19.207

3

When deleting rows, you will want to step backwards:

For i = 5000 to 1 step -1
    If InStr(UCase(Cells(i, 1).Value), "GUACAMOLE") > 0 Then 
        Rows(i).EntireRow.Delete    
    End If
Next i

edited Aug 22 '19 at 07:21

answered Aug 22 '19 at 06:42

braX

11,506
5
20
33

invalid procedure call or argument – Ozgar Aug 22 '19 at 07:09

How to delete rows in a loop?

1 Answers1

Linked