Delete Completely Empty Rows from Excel Tables with VBA in WPS Office
Question details
The user needs a VBA macro that checks every named Excel table and deletes only rows where all cells in the data row are completely empty.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A workbook contains named Excel tables with blank data rows, and the user wants to clean them with VBA instead of deleting rows manually.
- Observed behavior
- The macro should evaluate the complete table row, not only a single column, so partially filled rows are preserved and only fully empty rows are removed.
Open the workbook that contains the named tables and confirm the target worksheet name in the VBA code matches your sheet. The macro only processes table data rows, so header rows and total rows are not affected.
Delete completely empty rows from all Excel tables by looping backward with VBA
Use this method when you want one macro to process every named table on a worksheet and delete only rows whose entire table range is empty.
The macro uses Application.CountA on each ListRow range. If CountA returns 0, no cell in that row contains a value, formula result, text, or error, so the row is safe to delete.
Looping from the last data row to the first prevents Excel from skipping rows after a deletion because deleting a row shifts lower rows upward but does not affect the already processed rows above.
In WPS Office Excel, go to the Developer tab and click Visual Basic, or press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the editor, click Insert > Module to create a new standard module where you can paste the macro.
Paste the following macro: Sub DeleteEmptyRowsForAllTables() Dim ws As Worksheet, tbl As ListObject, i As Long Set ws = ThisWorkbook.Worksheets("Sheet1") For Each tbl In ws.ListObjects If Not tbl.DataBodyRange Is Nothing Then For i = tbl.ListRows.Count To 1 Step -1 If Application.CountA(tbl.ListRows(i).Range) = 0 Then tbl.ListRows(i).Delete Next i End If Next tbl End Sub
Place the cursor inside the macro and press F5, or click Run > Run Sub/UserForm. Confirm that the active workbook contains the named tables you want to clean.
Delete completely empty Excel table rows with a VBA macro in WPS Office
WPS Office Spreadsheets includes VBA tools that can run the same ListObjects loop as Excel. Open the Visual Basic Editor, insert a module, paste the macro, and run it to remove all fully empty rows from named tables in one pass.
- 1. Open the workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains the named Excel tables.
- 2. Open the Visual Basic Editor: Go to the Developer tab and click Visual Basic, or press Alt+F11. If the Developer tab is not visible, enable it from File > Options > Customize Ribbon.
- 3. Insert a module and paste the VBA code: Click Insert > Module and paste the macro that loops through tbl.ListRows from bottom to top and uses Application.CountA(tbl.ListRows(i).Range) = 0.
- 4. Run the macro: Press F5 with the cursor inside the macro, or click Run > Run Sub/UserForm, and verify that the target worksheet is selected.
Frequently Asked Questions
Why should the macro loop from bottom to top when deleting table rows?
Deleting from the last row upward keeps the indexes of earlier rows stable, so the macro does not skip rows after each deletion.
Does this VBA macro delete the header row or the totals row?
No. The macro only iterates through tbl.ListRows, which represents the data body range, so the header row and total row are not included.
Will a row with formulas that return blank-looking values be deleted?
No. Application.CountA counts cells that contain formulas, even if they display an empty string, so those rows are not considered completely empty.
How can I run this macro on all worksheets instead of only Sheet1?
Add an outer loop such as For Each ws In ThisWorkbook.Worksheets and then iterate ws.ListObjects inside that loop. Make sure each sheet is processed by the same backward row loop.
Do I need to save the workbook in a macro-enabled format?
Yes. To keep the VBA code, save the file in a macro-enabled format such as .xlsm or the WPS-compatible macro-enabled format; saving as a regular .xlsx file may remove the macro.
