How to Delete Rows When Column D Equals Zero in WPS Spreadsheets
Question details
The user needs a reusable Office Script-style macro that deletes every data row where column D contains zero, keeps row 1 as the header, and determines the used range automatically instead of using a hard-coded row count.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet has a header in row 1 and values in column D. The user wants to remove rows whose column D value is zero without manually selecting them and without deleting the header.
- Observed behavior
- The desired outcome is a macro that calculates the used range dynamically, processes rows from the bottom upward, and deletes only rows where column D equals zero while preserving row 1.
Open the workbook in WPS Spreadsheets and confirm that the values to evaluate are in column D and that row 1 contains the header. If you plan to run a macro, make sure the Developer tab is visible or use Alt+F11 to open the macro editor.
Use a Bottom-Up Macro to Delete Rows Where Column D Equals Zero
Best for recurring or large data sets because the macro reads the used range dynamically, checks both numeric and text zero values, and removes only the matching data rows.
This method processes rows from the last used row to row 2. Deleting from the bottom upward prevents row-index shifts from causing skipped rows, and the loop starts at row 2 so the header in row 1 is preserved.
In WPS Spreadsheets, click the Developer tab and select View Code, or press Alt+F11 to open the WPS macro editor.
In the macro editor, click Insert > Module to create a standard module where the VBA code can be placed.
Paste the following macro into the module. It reads the used range, starts at the last row, and deletes a row when column D is 0 or "0". Sub DeleteRowsWhereColumnDEqualsZero() Dim ws As Worksheet Dim lastRow As Long Dim firstRow As Long Dim i As Long Set ws = ActiveSheet firstRow = ws.UsedRange.Row lastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1 For i = lastRow To 2 Step -1 If Trim(ws.Cells(i, 4).Text) = "0" Or ws.Cells(i, 4).Value = 0 Then ws.Rows(i).Delete End If Next i End Sub
Click Run > Run Sub/UserForm or press F5 while the cursor is inside the macro, then select DeleteRowsWhereColumnDEqualsZero and click Run.
Filter and Delete Zero Rows Manually with AutoFilter
Use this for a one-off cleanup when you prefer not to run a macro and want to visually confirm the rows before deleting them.
Automate Zero-Row Deletion in WPS Spreadsheets
Use WPS Spreadsheets to run a macro that removes rows where column D equals zero. The macro reads the used range dynamically and processes rows from bottom to top, so your header stays intact and row shifts are handled automatically.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that contains the rows you want to clean.
- 2. Enable the macro tools: Click the Developer tab. If it is not visible, go to the ribbon settings or File > Options to enable the Developer tab.
- 3. Insert the macro code: Open the macro editor with Alt+F11, insert a module from Insert > Module, and paste the bottom-up deletion code.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm, select DeleteRowsWhereColumnDEqualsZero, and confirm that the zero rows are removed.
- 5. Save in an Excel-compatible format: Save the file as .xlsx, .xlsm, or .xls to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Why should the loop start from the bottom instead of the top?
When you delete a row, all later rows shift up. If you loop from top to bottom, the next row moves into the current index and can be skipped. Looping from the last used row to row 2 keeps the indexes stable and tests every row.
Does this macro remove the header row?
No. The loop starts at row 2 and runs backward to row 2, so row 1 is preserved even if the header cell in column D contains text or a zero.
What if column D contains text zeros such as "0"?
The macro checks both ws.Cells(i, 4).Text and ws.Cells(i, 4).Value, so numeric 0 and text "0" are both treated as matches. If you want an exact text-only match, remove the Value = 0 condition.
Can I change the code to check a different column?
Yes. Replace ws.Cells(i, 4) with the column number you need. For example, use ws.Cells(i, 5) for column E and adjust the code or message text accordingly.
Is there a safer alternative to permanently deleting rows?
Yes. Use Data > AutoFilter to display zero rows and then mark them as inactive, move them to an archive sheet, or copy visible rows elsewhere. This preserves the original data for audit or recovery.
