CODEWITHSUNDEEP
excelvba
71GA
71GA

Reputation: 1391

Deleting a row in a For loop messes the objects

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

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

This works until I uncomment the commented line which deletes the row.

Probably some objects get messed up upon deletion, because error says:

Application defined or object defined error.

Upvotes: 0

Views: 167

Answers (4)

spioter
spioter

Reputation: 1870

See my recommendation here that is more modular and deletes very fast https://stackoverflow.com/a/73322827/1279373

Upvotes: 0

Алексей Р
Алексей Р

Reputation: 7627

Solution without nested loop:

Option Explicit

Sub test1()
    Dim tbl As ListObject, i As Long, x As Range
    Set tbl = sh.ListObjects(1)
    
    For i = tbl.ListRows.Count To 1 Step -1
        Set x = Nothing
        On Error Resume Next
        Set x = tbl.ListRows(i).Range.SpecialCells(xlCellTypeConstants) 'not empty and not formulas
        On Error GoTo 0
        If x Is Nothing Then
            Debug.Print "DELETED " & i
            tbl.ListRows(i).Delete
        End If
    Next
End Sub

Upvotes: 0

Storax
Storax

Reputation: 12167

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

Upvotes: 1

Storax
Storax

Reputation: 12167

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

Upvotes: 1

Related Questions