Delete row loop vba

Question

I have a function in vba that loops through a set of data and checks if certain values exist and if they do, delete the row. It works but it doesn't delete all the values. I'm pretty sure it is because the loop's upper bound is the row count of the range which with every deletion, gets smaller. How would I change this? Any help would be greatly appreciated!

Function DeleteClients(rng1 As Range, rng2 As Range)

vData1 = rng1.Value

For i = 1 To rng2.Rows.Count

    For j = LBound(vData1, 1) To UBound(vData1, 1)

        If rng2.Cells(i, 1).Value = vData1(j, 1) Then
            rng2.Cells(i, 1).EntireRow.Delete
            Exit For
        End If

    Next j
Application.StatusBar = "Deleting out excluded clients... " & i & "/" & rng2.Rows.Count & " Records Processed " & Round((i / rng2.Rows.Count) * 100, 0) & " % Complete"
Next i

Application.StatusBar = False

End Function

Reverse your loop `For i = rng2.Rows.Count to 1 Step -1` will make you loop from bottom to top. — Kyle, Mar 04 '15 at 17:07

score 0 · Accepted Answer · answered Mar 04 '15 at 17:38

Kyle is right -- loop from bottom (rng2.Rows.Count) to top (1) with step -1, and this should work fine.

What's doing you in is that when you delete row 5, the old row 6 is the new row 5, but the next loop is looking at row 6 -- the old Row 7, so your old Row 6 (new Row 5) never gets considered. Looping from bottom to top solves this, because if you delete Row 5, the next row to be considered is Row 4, and it hasn't moved.

Delete row loop vba

1 Answers1

Linked