How to Fix SpecialCells Not Deleting Rows with Formula Results in WPS Spreadsheets
Question details
The user runs a VBA macro that tries to delete rows using SpecialCells(xlCellTypeConstants, 4), but the helper column contains formulas that return the target value, so SpecialCells finds no cells and no rows are deleted.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Automate row deletion when a helper column formula evaluates to a specific value.
- Observed behavior
- SpecialCells returns no cells because it only considers constant values, not formula results; the matching rows remain in the worksheet.
Save the workbook as a macro-enabled file and confirm that the helper column, for example column J, contains the formulas you intend to convert before running the macro.
Convert the Helper Column Formulas to Values Before Using SpecialCells
Use this when you want to keep the existing SpecialCells deletion code and make the helper column results visible as constants.
xlCellTypeConstants identifies only cells that contain literal values. If column J contains formulas returning 2, the cells are not considered constants, even when the displayed result is 2. Converting them to values makes the results visible to SpecialCells.
In WPS Spreadsheets, open the workbook and select column J, then press Ctrl+C to copy the helper column.
Right-click column J and choose Paste Special > Values, or run Columns("J:J").PasteSpecial Paste:=xlPasteValuesAndNumberFormats in the VBA editor.
Run your existing code that calls SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete; the matching rows will now be detected and deleted.
Delete Rows by Looping Through Formula Results
Use this alternative when you cannot convert the helper column to values and need to keep the formulas intact.
Delete Rows with Formula Results in WPS Spreadsheets VBA
Use WPS Spreadsheets to paste helper formulas as values and run your SpecialCells macro, or loop through formula results directly. WPS Office provides familiar VBA support so Excel macro workflows can be completed in a free, lightweight app.
- 1. Open the macro-enabled workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsm workbook that contains the VBA deletion macro.
- 2. Convert the helper column to values: Select the helper column, such as column J, and click Home > Paste > Values to replace formulas with their results.
- 3. Open the VBA editor: Press Alt+F11 and locate the existing SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete macro.
- 4. Run the macro: Press F5 to run the macro; WPS Spreadsheets deletes the rows where the helper column now contains the matching constant value.
Frequently Asked Questions
Does SpecialCells work with formula results?
No. xlCellTypeConstants only returns cells containing literal values, so a formula that returns the same value is not included. Convert formulas to values or use a loop to evaluate formula results.
How do I convert formula results to values in WPS Spreadsheets?
Select the helper column, press Ctrl+C, then right-click and choose Paste Special > Values. In VBA, use Columns("J:J").PasteSpecial Paste:=xlPasteValuesAndNumberFormats.
Can I delete rows based on formula results without changing formulas?
Yes. Loop through the cells from the last row upward and delete rows where the cell value matches, for example If Range("J" & i).Value = 2 Then Rows(i).Delete.
Why does my SpecialCells code return no cells?
Because the column likely contains formulas, not constants. SpecialCells(xlCellTypeConstants, 4) only detects constant values, so it skips formula results even if the displayed value is the target.
Does WPS Spreadsheets support Excel VBA macros?
Yes. WPS Office supports VBA macros and can open macro-enabled Excel files, but some advanced Excel-specific objects may behave differently, so test your macro in WPS Spreadsheets.
