How to Filter Excel Data to Exclude Values and #N/A Errors Using VBA
Question details
The user needs to write a VBA macro to filter a dataset to exclude both specific ordinary values and formula errors like #N/A.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data filtering via VBA to clean up a spreadsheet by hiding rows that contain specific unwanted numbers or #N/A error values.
- Observed behavior
- While standard not-equal criteria in VBA AutoFilter successfully hide ordinary values, using Criteria1:="<>#N/A" fails to exclude #N/A formula errors.
Ensure that the Developer tab is enabled in your ribbon to access the Visual Basic Editor, and confirm the exact column index that contains the target data and potential #N/A errors.
Use a Helper Column and VBA to Filter Out #N/A Errors
Since AutoFilter struggles to interpret error values directly via VBA string criteria, creating a temporary helper column with the ISNA function is the most reliable method to identify and filter them out.
VBA treats AutoFilter criteria as strings. Because #N/A is an error type rather than a text string, the standard not-equal operator does not evaluate it properly. Using a helper column transforms the error check into a simple boolean (TRUE/FALSE) value.
In an empty adjacent column (e.g., Column C), insert a formula to check for the error, such as `=ISNA(B2)`, and fill it down to the bottom of your dataset.
Write a VBA script to apply an AutoFilter on this new helper column. Use `Criteria1:="FALSE"` to display only the rows that do not contain the #N/A error.
Run the macro. The #N/A errors will be hidden. You can then add code to hide the helper column from view to keep the spreadsheet clean.
Exclude Ordinary Values with Not-Equal Criteria
For standard text or numerical values, you can use the built-in VBA AutoFilter operators directly without needing a helper column.
Filter and Manage Data with Macros in WPS Spreadsheet
WPS Office Spreadsheet provides robust support for VBA and macros, allowing you to easily automate data filtering tasks, handle errors like #N/A, and manage large datasets smoothly.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the file containing the data and errors.
- 2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' or 'Macros'.
- 3. Run the Filtering Macro: Paste your AutoFilter and helper column VBA code into the editor and press Run to instantly filter out the unwanted values and #N/A errors.
Frequently Asked Questions
Why doesn't Criteria1:="<>#N/A" work in Excel VBA AutoFilter?
AutoFilter in VBA treats criteria as text strings. Since #N/A is a specialized error data type rather than a standard text string, the filter fails to evaluate it correctly, meaning the error rows remain visible.
Can I filter out blank cells using VBA AutoFilter?
Yes, you can easily filter out blank cells in a specific column by setting your VBA AutoFilter criteria to `Criteria1:="<>"`.
How do I clear an active AutoFilter using VBA?
You can clear existing filters and show all data by using the command `ActiveSheet.ShowAllData`. Alternatively, you can toggle the filter off by using `Selection.AutoFilter` without any parameters.
