VBA -- deleting a row in a FOR loop messes the objects-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

The other solution would be, as always, to step backwards through the rows

Sub loDel()
    Dim tbl As ListObject
    Set tbl = sh.ListObjects(1)
    Dim d As Integer
    Dim i As Long
    Dim c As Range
    For i = tbl.ListRows.Count To 1 Step -1
        d = 1
    
        Dim rg As Range
        Set rg = tbl.ListRows(i).Range
        For Each c In rg
            If IsEmpty(c) = False And c.HasFormula = False Then
                d = 0
            End If
        Next
        
        If d = 1 Then
            Debug.Print "DELETE", i
            tbl.ListRows(i).Delete
        End If
    Next

End Sub