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

Egan Wolf
– Egan Wolf

2017-08-09 08:40:24 +00:00
Commented Aug 9, 2017 at 8:40 — Egan Wolf
– Egan Wolf, Commented Aug 9, 2017 at 8:40

Rik Sportel · Accepted Answer · 2017-08-09 08:49:28Z

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 9, 2017 at 8:49

answered Aug 9, 2017 at 8:30

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Egan Wolf Over a year ago

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 answer for more details.

Matt555 Over a year ago

Thanks Rik Worked like a dream!

Egan Wolf Over a year ago

@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.

Rik Sportel Over a year ago

@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.

Collectives™ on Stack Overflow

VBA Loop and delete issue

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related