VBA — deleting a row in a FOR loop messes the objects

August 10, 2022

I have a table tbl which is a ListObject. I want to delete rows that only have empty values or equations.

This is my solution:

Dim tbl As ListObject
Set tbl = sh.ListObjects(1)
Dim r As ListRow
Dim c As Range
Dim d As Integer

For Each r In tbl.ListRows
        d = 1
        For Each c In r.Range.Cells
            If IsEmpty(c) = False And c.HasFormula = False Then
                d = 0
            End If
        Next
        
        If d = 1 Then
            Debug.Print "DELETE", r.Index
            '''' rows(r.Index).EntireRow.Delete
        End If
Next

Problem here is that this works until I uncomment the the commented line which actually deletes the row. Probably some objects get messed up upon deletion, because error says:

Application defined or object defined error.

Does anyone have an idea?

>Solution :

As suggested in one of the comments one could collect the rows to be deleted

Sub loDel()
    Dim tbl As ListObject
    Set tbl = sh.ListObjects(1)
    Dim r As ListRow
    Dim c As Range
    Dim d As Integer

    Dim dRg As Range

    For Each r In tbl.ListRows
    
        d = 1
        For Each c In r.Range.Cells
            If IsEmpty(c) = False And c.HasFormula = False Then
                d = 0
            End If
        Next
        
        If d = 1 Then
            Debug.Print "DELETE", r.Index
            If dRg Is Nothing Then
                Set dRg = r.Range    
            Else
                Set dRg = Union(dRg, r.Range)
            End If
            'Rows(r.Index + 1).EntireRow.Delete
        End If
    Next
    
    If Not dRg Is Nothing Then
       dRg.Rows.Delete
    End If 
End Sub