Forum Discussion

rodsan724's avatar
rodsan724
Brass Contributor
Sep 19, 2023

VBA How do I delete entire row where all cells are empty for row

How do I delete entire row where all cells are empty for all named tables?

 

I tried the following:

 

Sub DeleteEmptyRowsForAllTables(tableName)
    Range(tableName & "[[Test]]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

However, it only considers a single column which isn't what I need. Is there a way to consider a range of cells?

 

 

Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 20, 2023

    rodsan724 

    To delete entire rows where all cells are empty for all named tables in Excel VBA, you can loop through each table and check each row within the table to see if all cells are empty. If all cells in a row are empty, you can delete the entire row. Here is a VBA code snippet that accomplishes this:

    vba code (untested):

    Sub DeleteEmptyRowsForAllTables()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim row As ListRow
    
    ' Specify the worksheet containing the tables
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    
    ' Loop through all tables in the worksheet
    For Each tbl In ws.ListObjects
        ' Define the range of the table (excluding header row)
        Set rng = tbl.DataBodyRange
        
        ' Loop through each row in the table
        For Each row In rng.Rows
            ' Check if all cells in the row are empty
            If Application.WorksheetFunction.CountA(row) = 0 Then
                ' Delete the entire row
                row.Delete
            End If
        Next row
    Next tbl
End Sub

    Here is how this code works:

    1. It specifies the worksheet (ws) where your named tables are located. You should change "Sheet1" to the name of your worksheet.
    2. It loops through each named table in the worksheet using a For Each loop.
    3. For each table, it defines a range (rng) that covers the entire data body of the table, excluding the header row.
    4. It then loops through each row within the defined range.
    5. For each row, it checks if all cells in the row are empty using Application.WorksheetFunction.CountA(row) = 0. If all cells are empty, it deletes the entire row.

    You can run this VBA macro in Excel to delete empty rows in all named tables on the specified worksheet. Just make sure to update the worksheet name as needed.

     

    To delete entire rows where all cells are empty for all named tables in Excel without using VBA, you can use a combination of filters, sorting, and manual deletion.

     

    The text, the code and steps were edited with the help of AI.

     

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources