Excel VBA – Remove blank rows in table

calendar_today Asked Oct 20, 2012

thumb_up 6 upvotes

history Updated April 16, 2026

Question posted 2012 · +6 upvotes

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 a run-time error 1004 - "Delete method of Range class failed".

Any ideas how to fix this problem? Thanks

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

Accepted answer +6 upvotes

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

Excel VBA objects referenced (4)

EntireRow.Delete — SolverDelete Function
EntireRow.Delete — Delete Duplicate Entries in a Range
Range — Refer to Cells by Using a Range Object
Selection — Working with the Selection Object

Top `excel-vba` Q&A (6)

How to clear the entire array? +58 (2010)
How to change Format of a Cell to Text using VBA +55 (2011)
Download attachment from Outlook and Open in Excel +43 (2012)
Can a VBA function in Excel return a range? +36 (2009)
2 Dimensional array from range +34 (2013)
Hiding an Excel worksheet with VBA +33 (2009)

+6 upvotes ranks this answer #114 out of 136 excel-vba solutions on this site .