VBA Loop and delete issue

Question

I have created some coding which is designed to loop through a data set and delete rows based on the required criteria. I need help as it deletes the first found matching criteria but doesn't loop through the rest of the data. What am I missing? Many Thanks

Sub RemoveFivePoundException()

Dim I As Integer
Dim LR As Long

Application.ScreenUpdating = False


Sheets("Claims").Select
LR = Cells(Rows.Count, "A").End(xlUp).row
Range("a1").Select

 For I = 3 To LR
    If Cells(I, 6).Value > 5# And Cells(I, 7) = "Under £5 write off" Then
       Cells(I, 1).EntireRow.Delete
    End If

  Next I

 Application.ScreenUpdating = True

 End Sub

Possible duplicate of [Excel VBA Delete Rows](https://stackoverflow.com/questions/35042717/excel-vba-delete-rows) — Egan Wolf, Aug 09 '17 at 08:40

Rik Sportel · Accepted Answer · 2017-08-09T08:49:28.757

0

When deleting rows, you should invert your loop. With every row deletion the index of the next row has changed.

Alter your loop to:

For I = LR To 3 step -1 'Invert loop!
    If Cells(I, 6).Value > 5# And Cells(I, 7) = "Under £5 write off" Then
       Cells(I, 1).EntireRow.Delete
    End If
Next I

Alternatively, you can do:

For I = 3 To LR
    If Cells(I, 6).Value > 5# And Cells(I, 7) = "Under £5 write off" Then
        Cells(I, 1).EntireRow.Delete
        I = I - 1 'To avoid skipping rows.
    End If
Next I

As per comments below this second option works, but is bad practice.

edited Aug 09 '17 at 08:49

answered Aug 09 '17 at 08:30

Rik Sportel

2,661
1
14
24

Your alternative solution is not a good option. In that case you will process more rows than from 3 to LR, exactly 1 more row for every deleted row. Check [this](https://stackoverflow.com/a/45568605/5722505) answer for more details. – Egan Wolf Aug 09 '17 at 08:38
Thanks Rik Worked like a dream! – Matt555 Aug 09 '17 at 08:43
@RikSportel It works, but sometimes it works "better than expected". If you have more data below the range you loop through, you can delete some important rows. – Egan Wolf Aug 09 '17 at 08:53
@EganWolf Also true, since you'll loop until whatever was initially set as the row to loop to whilst changing the indexes of the rows afterwards. When those rows meet the criteria for deletion they'll be gone of course. It's also true that when you loop to "lastrow" the last "possible deletions" would be on empty rows. It's still extra IO operations and thus additional overhead that you don't want. There are cases in which you might want to loop over the initial range even if indexes change, but these are rare and usually involve generating extra data on the spot in the first place. – Rik Sportel Aug 09 '17 at 08:58

VBA Loop and delete issue

1 Answers1