Why do I have to run my for each loop multiple times to delete rows in a table?

Question

I am trying to delete rows in a table on an excel sheet with multiple tables based on the values in each row. After much research and trial and error, I have gotten my macro to work except I have to run it multiple times to actually delete all of the "bad" rows. Here's what I have so far:

Sub RemovePartsOfTable()

Dim row As Range

For Each row In ListObjects("Table6").DataBodyRange.Rows 

    If row.Columns(6).Value = "" Then 'to exit when it hits the end of the used cells
        Exit For

    ElseIf row.Columns(1).Value <> "P" And row.Columns(1).Value <> "B" _
        And row.Columns(2).Value <> "B" _
        And row.Columns(2).Value <> "P" Then
        row.Delete
    End If

Next

End Sub

The macro does delete all of the rows that I don't want in the table if I run it five times, but I would rather not have to run it more than once since I'm building this workbook for someone else to use too.

either run a reverse loop or just union all the rows to be deleted and delete in one go. — cyboashu, Aug 05 '16 at 20:01
See here for many methods you can modify: http://stackoverflow.com/questions/33744149/code-in-vba-loops-and-never-ends-how-to-fix-this — Scott Craner, Aug 05 '16 at 20:02
http://stackoverflow.com/questions/20077945/delete-cells-in-an-excel-column-when-rows-0 — Siddharth Rout, Aug 05 '16 at 20:16
Maybe this can be of help: http://stackoverflow.com/questions/36873359/fastest-way-to-delete-rows-which-cannot-be-grabbed-with-specialcells — Ralph, Aug 06 '16 at 01:01

score 0 · Accepted Answer · answered Aug 06 '16 at 11:08

The solution is to put the For loop inside a Do loop; then the Do loop is repeated while in the For loop some deletion is done.

Option Explicit

Sub RemovePartsOfTable()
Dim flagNoRowDeletedInLoopCycle As Boolean
Dim row As Range

Do

    flagNoRowDeletedInLoopCycle = False

    For Each row In ActiveSheet.ListObjects("Table6").DataBodyRange.Rows

        If row.Columns(6).Value = "" Then 'to exit when it hits the end of the used cells

            Exit For

        ElseIf row.Columns(1).Value <> "P" And row.Columns(1).Value <> "B" _
            And row.Columns(2).Value <> "B" _
            And row.Columns(2).Value <> "P" Then

            row.Delete

            flagNoRowDeletedInLoopCycle = True

        End If

    Next

    If flagNoRowDeletedInLoopCycle = False Then Exit Do

Loop

End Sub

Thanks! This is exactly the kind of fix I was looking for. – cscho Aug 08 '16 at 23:09 — cscho, Aug 08 '16 at 23:09

Why do I have to run my for each loop multiple times to delete rows in a table?

1 Answers1