WPS Spreadsheets: Automatically Hide or Delete Rows by Status
Question details
The user needs to hide or delete rows when columns E, G, and I contain specific status values at the same time.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet tracks statuses in columns E, G, and I. Rows should be hidden or removed when E is "completed," G is "completed," and I is "discarded."
- Observed behavior
- Rows are not automatically hidden or deleted when the three status conditions are met.
Check whether the status cells are manually entered or calculated by formulas, because manual edits use a worksheet-change event while formula results require a calculation event. Add a helper column and test the condition before deleting any rows.
Use a helper formula and filter matching rows
Use this safer approach when the statuses may be formula-generated or when you want to review matching rows before hiding or deleting them.
A helper column evaluates all three conditions in one place. Filtering for TRUE works for both manually entered values and formula results and avoids the irreversible effects of deleting rows.
In an unused helper column, such as J2, enter =AND(E2="completed",G2="completed",I2="discarded"), then press Enter and fill the formula down the data range.
Select the header row and data range, open the Data tab, and choose AutoFilter or Filter.
Open the filter arrow in the helper column and select TRUE. WPS Spreadsheets will display only rows that meet all three status conditions.
Leave the filtered rows hidden by the filter, or select the visible row headers, right-click, and choose Delete Rows only after confirming the results.
Automatically hide rows with a WPS VBA event
Use this option when manually entered statuses should trigger row hiding immediately after a cell is changed and VBA is available in your WPS installation.
Delete qualifying rows with a controlled macro
Use this only when matching rows must be permanently removed and you have confirmed the condition against a copy of the worksheet.
Automate Status-Based Row Filtering in WPS Spreadsheets
WPS Spreadsheets lets you evaluate multiple status conditions with an AND formula, filter matching records, and use VBA events when supported to hide or delete rows automatically. Its familiar spreadsheet interface makes it easy to review results before taking an irreversible action.
- 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the status columns.
- 2. Create a status test: In a helper column, enter =AND(E2="completed",G2="completed",I2="discarded") and fill it down the table.
- 3. Filter matching records: Select the table, open Data > AutoFilter, and filter the helper column for TRUE.
- 4. Review the filtered rows: Check the visible records before choosing to leave them filtered, hide their rows, or delete them.
Frequently Asked Questions
What formula checks all three status values in one row?
Enter =AND(E2="completed",G2="completed",I2="discarded") in a helper column such as J2, then fill it down. The result is TRUE only when all three conditions are met.
Should I use Worksheet_Change or Worksheet_Calculate?
Use Worksheet_Change when users type or paste the statuses directly into columns E, G, or I. Use Worksheet_Calculate when those cells contain formulas whose results can change after recalculation.
Can I hide matching rows without using VBA?
Yes. Add the AND helper formula, enable Data > AutoFilter, and filter the helper column for TRUE. The nonmatching rows remain visible while matching rows are hidden by the filter.
How can I delete only the rows that match the statuses?
Filter the helper column for TRUE, verify the visible rows, select their row headers, right-click, and choose Delete Rows. For repeated automation, use a VBA macro that checks rows from the bottom upward.
Why is filtering recommended before deleting rows?
Filtering allows you to review every matching record and can be cleared or changed later. Deleting rows removes the data and may be difficult to undo, especially when a macro performs the deletion.
