excelvba

Remove blank rows in table

I'm trying to run a macro that selects blank cells in a table column and deletes the entire row.

The script below does everything except the deleting part, which prompts the following error:

run-time error 1004 - "Delete method of Range class failed"

I have used the following code:

Sub test()
Range("Table1[[New]]").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub
Solution

  • Nice question! Without a table, .EntireRow.Delete always works, but inside a table it looks like as it doesn't.

    This works:

    Sub Test()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table1[[New]]").SpecialCells(xlCellTypeBlanks)
  On Error Goto 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If
End Sub