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?
This is how to delete row number 5 from a table named
TableName
: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 beforeUrng.Delete
to see yourself:At the sample you may see that the row
6
is in the table and row18
is outside the table: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: