Search code examples
excelvbafor-loopdelete-rowset-union

Excel VBA Can't delete entire row when part of row is a table

I'm trying to loop through my data and Union certain row numbers that I need to delete later on. The code below stores the correct rows, but I can't delete them. I believe it's because my data is arranged in a table, since I'm able to delete the desired rows if the data is not in a table. I get the error message 'run time error 1004 - delete method of range class failed' on the line Urng.delete.

Sub DeleteRows()
Dim ws4 As Worksheet: Set ws4 = Worksheets("Sheet1") 
Dim LastRow As Long 
Dim CurrentRow As Long
Dim GroupValue
Dim GroupTotal As Long
Dim x As Long
Dim Urng As Range

Application.ScreenUpdating = False
ws4.Activate

GroupValue = ws4.Range("B6").Value
CurrentRow = 6     LastRow = ws4.Cells(Rows.Count, "B").End(xlUp).Row
Set Urng = Rows(LastRow + 1)

    For x = 1 To LastRow 
        GroupTotal = Application.WorksheetFunction.CountIf(Range("B6:B" & LastRow), GroupValue)
        If GroupTotal = 1 Then
            Set Urng = Union(Urng, Rows(CurrentRow))
        End If

        CurrentRow = CurrentRow + GroupTotal
        GroupValue = Range("B" & CurrentRow).Value     
        If GroupValue = "" Then ' 
            Exit For
        End If

    Next x

Urng.Delete
Application.ScreenUpdating = True
End Sub

I've tried using .EntireRow.Delete without luck.

There's no data outside the table, so deleting just the table rows could be a solution, however, I don't know how to build the loop that Unions the row numbers if I can't use the row number in Union(Urng, Rows(CurrentRow)).

Is there a VBA-solution to delete multiple entire rows, where part of the row is a table?

Solution

  • This is how to delete row number 5 from a table named TableName:

    Sub TestMe()
    Range("TableName[#All]").ListObject.ListRows(5).Delete
End Sub

    Concerning your specific problem, the case is that in Urng you are having rows, which are both in and outside the table. Thus, they cannot be deleted with .Delete. Write this before Urng.Delete to see yourself:

    Urng.Select
Stop
Unrg.Delete

    At the sample you may see that the row 6 is in the table and row 18 is outside the table:

    enter image description here

    Concerning deletion of two rows, which are not close to each other in a table, I guess that the only way is to loop. It is a bit slower indeed, but it works:

    Sub TestMe()

    Dim cnt As Long
    Dim arrRows As Variant: arrRows = Array(10, 12)
    Dim table As ListObject: Set table = ActiveSheet.ListObjects("SomeTable")

    For cnt = UBound(arrRows) To LBound(arrRows) Step -1
        table.ListRows(arrRows(cnt)).Delete
    Next cnt

    'This works only when the rows are after each other, e.g. 2,3,4
    table.Range.Rows("2:4").Select
    Stop
    table.Range.Rows("2:4").Delete

End Sub