Remove blank rows in table

Question

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

Answer 1

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

Answer 2

You actually can do it in one pass, but need to use the ListObject object and its DataBodyRange and ListColumns properties:

Sub ClearBlankCellsInColumnNew()
Dim rngBlanks As Excel.Range

With Worksheets("Sheet1").ListObjects("Table1")
    On Error Resume Next
    Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("New").Range).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rngBlanks Is Nothing Then
        rngBlanks.Delete
    End If
End With
End Sub

Answer 3

Step 1: Make a helper column in the table where you check for any blank fields in that row. For example, if you had 3 columns in your table: A (Price), B (Quantity), and C (Cost), you would add a fourth column D and label it "Any Blanks?". The equation would be =IF(OR(ISBLANK([@Price]),ISBLANK([@Quantity]),ISBLANK([@Cost])),"Yes","No")

That would give you a column to filter to view all the blanks.

Step 2: In VBA you would then do the following:

Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column, Criteria1:="Yes"
Application.DisplayAlerts = False
Range("MyTableNameHere").ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column

This essentially is filtering to the rows you want to delete in the table using the helper column, selecting all the visible data in the table, and unfiltering the table. I was searching around how to delete all visible rows in a table and found this and fiddled around until I figured out that this would work. Taking that and combining it with a helper column to select all rows with any blanks seems like what you were wanting as well.

Answer 4

Adapting previous answers:

On Error Resume Next
Set Rng = ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.Delete Shift:=xlUp
End If

Answer 5

This One liner also would help

on error resume next 'to continue macro if no empty row is found in table .Range("Table1").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'this delete empty using 1st column as reference for checking blanks 'Use custom names for tables for easy manipulation in codes

Answer 6

Using ListObjects in Excel makes it easy to use the following to remove blank rows.

Sub RemoveBlankRow(ByVal SheetName As String, TableName As String)
Dim rng As Integer

rng = Sheets(SheetName).ListObjects(TableName).DataBodyRange.Rows.Count

For i = 1 To rng
    If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete
Next
End Sub

Answer 7

Two notes regarding Frej Lindstrom's solution, which I used but had to tweak a little:

(1) add an End If before the Next

(2) add "i = i - 1" just before the End If

Why? because if you have blank rows one above each other, you'll skip one since all the rows' numbers just shifted one up. Essentially, if you deleted row [N], another row is now row [N], and you need to also test it rather than moving immediately to row [N + 1].

BIG CAVEAT: if your last row is blank, this will give you a stuck loop. I'll probably put in an IF to handle it, though.

I know this is an old thread, but thought I'd add this in case anyone else comes through looking for similar solutions. Thank you to Frej - your code really helped!

Answer 8

This is my solution:

Public Sub remove_blank_rows(sh As Worksheet)

    Dim tbl As ListObject
    Set tbl = sh.ListObjects(1)
    Dim i As Long
    Dim ii As Long
    
    Application.EnableEvents = False
    
    ii = tbl.Range.rows.Count
    For i = 2 To ii
        If WorksheetFunction.CountA(rows(i)) = 0 Then
            
            rows(i).EntireRow.Delete
            
            ii = tbl.Range.rows.Count
            If i >= ii Then
                GoTo A
            End If
            
            If i > 1 Then
                i = i - 1
            End If
    
        End If
    Next
A:
    
    Application.EnableEvents = True
End Sub

Note that when you delete a row, all the rows below it will shift up by 1 and this is why index i is decremented in order to again check the same row in the next iteration! Note however that i need not exceed certain limits and this is done with the two If sentances.