CODEWITHSUNDEEP
excelvba
user91240192094
user91240192094

Reputation: 408

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

Upvotes: 15

Views: 49564

Answers (9)

Hal Meyer
Hal Meyer

Reputation: 1

Thanks for the collaboration. I kept getting an extra blank row, thus I had to make a slight modification. In summary, the DeleteBlankTableRows macro uses the GetMyTableName UDF to obtain the table name from the active cell. If the active cell is not within the table, "N/A" is returned and a message is sent to the user to select any cell within the table.

Option Explicit

Function GetMyTableName(myActiveCell As Range) As String
    Dim myTableName As String

    myTableName = ""
    On Error GoTo ErrorHndler
    myTableName = myActiveCell.ListObject.Name
    GetMyTableName = myTableName
    Exit Function

ErrorHndler:
    GetMyTableName = "N/A"
End Function

Sub DeleteBlankTableRows()
    Dim myBook As Workbook, mySheet As Worksheet, myRange As Range 
    Dim myActiveCell As Range, myTableName As String
    Dim i As Long, j As Long, myCount As Long, myTable As ListObject

    Set myBook = ActiveWorkbook
    Set mySheet = myBook.ActiveSheet
    Set myActiveCell = ActiveCell
    myTableName = GetMyTableName(myBook.Worksheets(mySheet.Name).Range(myActiveCell.Address))

    If Not myTableName = "N/A" Then
        Set myTable = myBook.Sheets(mySheet.Name).ListObjects(myTableName)
        i = myTable.Range.Rows.Count
    
        For j = 2 To i
            If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
                Rows(j).EntireRow.Delete
            
                i = myTable.Range.Rows.Count
                myCount = myCount + 1
                If j > i Then
                    Exit For
                End If

                j = j - 1
            End If
        Next j
        MsgBox myCount & " blank rows deleted. " & Chr(10) & Chr(10) & _
            "TableName: " & myTableName & Chr(10) & _
            "Worksheet: '" & mySheet.Name & "'" & Chr(10) & _
            "TableRange: " & myTable.Range.Address, _
            vbInformation + vbOKOnly, "DeleteBlankTableRows Macro"
    Else
        MsgBox "Cell " & myActiveCell.Address & " is not within a table. Please select a cell within the table and run this macro again.", vbInformation + vbOKOnly, "DeleteBlankTableRows Macro"
    End If
End Sub

Upvotes: 0

71GA
71GA

Reputation: 1391

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.

Upvotes: 0

Prabhu
Prabhu

Reputation: 11

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

Upvotes: 1

Rebecca
Rebecca

Reputation: 3

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!

Upvotes: 0

Frej Lindström
Frej Lindström

Reputation: 1

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

Upvotes: 0

rnsousa
rnsousa

Reputation: 11

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

Upvotes: 1

user4855028
user4855028

Reputation:

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.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

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

Upvotes: 3

fthiella
fthiella

Reputation: 49049

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

Upvotes: 16

Related Questions