How to Delete Excel Table Rows in WPS Spreadsheets When a Formula Returns Blank
Question details
The user needs a VBA macro that deletes Excel table rows when a target cell contains a formula returning an empty string, without triggering Runtime Error 1004.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is maintaining an Excel table where a column contains formulas that return empty strings and wants to remove rows whose formula result is blank.
- Observed behavior
- A forward VBA loop deletes rows while iterating, so the range shifts and Excel raises Runtime Error 1004; formula cells returning "" are also not detected as truly blank by IsEmpty.
Confirm that the worksheet name, table name, and target column header in the macro match the workbook exactly. Because the cells contain formulas that return empty strings, use Len(cell.Value) = 0 rather than IsEmpty to identify them.
Loop Through the Table Bottom-Up and Delete Matching ListRows
Use this method to safely remove Excel table rows whose formula result is an empty string while avoiding the range-shift error caused by top-down deletion.
Deleting from the last row upward keeps the remaining row indexes stable, so the loop does not skip rows or reference a removed range.
Testing Len(cell.Value) = 0 catches both zero-length strings and truly empty cells, which is useful when a column contains formulas that return "".
In WPS Spreadsheets, open the workbook and press Alt+F11 to open the VBA editor.
Go to Insert > Module, then declare the Workbook, Worksheet, and ListObject variables for the target table.
Use Set ws = ThisWorkbook.Worksheets("Test") and Set tblTest = ws.ListObjects("Test_T"), then point to tblTest.ListColumns("Child #").DataBodyRange.
Write For i = tblTest.ListRows.Count To 1 Step -1 and test If Len(tblTest.ListColumns("Child #").DataBodyRange.Cells(i).Value) = 0 Then.
Inside the condition, call tblTest.ListRows(i).Delete to remove only the table row.
Collect Matching Rows First and Delete Them After the Loop
Use this alternative when you prefer to keep a top-down loop but still want to avoid changing the range while it is being iterated.
Remove Blank Formula Rows from an Excel Table in WPS Spreadsheets
Run a bottom-up VBA ListRows loop in WPS Spreadsheets to delete only the table rows whose formula returns an empty string, keeping the table structure intact and avoiding Runtime Error 1004.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel-compatible workbook that contains the table and formulas.
- 2. Insert the bottom-up VBA ListRows macro: Press Alt+F11 to open the VBA editor, insert a module, and paste the macro that loops from ListRows.Count To 1 Step -1 and deletes rows where Len(...) = 0.
- 3. Run the macro and verify the table: Run the macro and confirm that only rows whose formula returns "" are removed while the Excel table object and data remain intact.
Frequently Asked Questions
Why does deleting rows in a forward loop cause Runtime Error 1004?
When you delete a row while looping from top to bottom, the remaining rows shift upward, so the loop can refer to a row that no longer exists or skip rows. Loop from ListRows.Count To 1 Step -1 to keep the index stable.
Should I use IsEmpty to test a formula returning an empty string?
No. IsEmpty returns False for cells that contain a formula, even if the formula result is a zero-length string. Use Len(cell.Value) = 0 instead.
Does tblTest.ListRows(i).Delete remove worksheet rows outside the table?
No. It deletes the table row from the ListObject only, so cells outside the table range are not directly deleted.
How do I run this VBA macro in WPS Spreadsheets?
Open the workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, insert the code into a module, and run the macro from the Macro dialog or the VBA editor.
