VBA & Macro Problems

How to Use VBA AutoFilter to Filter #N/A Formula Results in Excel

Guest Writer Oct 10, 2026 869 views

Question details

The user needs to filter out or display `#N/A` errors generated by a VLOOKUP formula using VBA AutoFilter, but the macro fails to recognize the formula's error results as standard filterable values.

How to Filter #N/A Formula Results Using VBA AutoFilter

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating dataset filtering with VBA on columns containing VLOOKUP functions that occasionally return error values.
Observed behavior: Standard VBA AutoFilter criteria do not consistently recognize the `#N/A` results generated by formulas, leading to incorrect or empty filtered views.

Before you start

Verify whether the #N/A values in your column are true Excel formula errors generated by functions like VLOOKUP, or if they are simply typed text strings, as this determines which filtering method will work best.

Solution 1Recommended

Use IFERROR to Convert Errors to Text (Most Reliable)

Wrap your VLOOKUP formula in an IFERROR function to output a standard text string. This ensures VBA can easily and consistently filter the value regardless of Excel language settings.

True formula errors can behave unpredictably in VBA AutoFilter, especially across different localized versions of Excel. Converting the error to a plain text string eliminates this issue entirely.

Modify the VLOOKUP Formula

Select the cell containing your VLOOKUP formula and update it to include IFERROR. For example: =IFERROR(VLOOKUP(AQ162, 'Smartlist - Vendor'!AS:AS, 1, FALSE), "N/A"). Drag the formula down to apply it to the entire column.

Update VBA AutoFilter Criteria

Open the VBA Editor (Alt + F11) and locate your AutoFilter script. Change your criteria to target the new text string. For example: .AutoFilter Field:=36, Criteria1:="N/A".

Combine with Multiple Criteria

If you need to filter by a date or another condition alongside the N/A text, use the xlOr or xlAnd operator. Example: .AutoFilter Field:=36, Criteria1:=">=" & dte, Operator:=xlOr, Criteria2:="N/A".

Use IFERROR to Convert Errors to Text (Most Reliable)

Language Independence: Using the IFERROR method guarantees your macro will work across all language versions of Excel, as it targets a hardcoded text string rather than a system-generated error.

Solution 2

Filter the #N/A Error Directly Using VBA

If you cannot change the worksheet formulas, you can configure the VBA AutoFilter criteria to look specifically for the system's #N/A error string.

Seamless VBA Support

Easily Run and Edit VBA Macros in WPS Office

WPS Spreadsheets provides robust support for Microsoft Excel VBA macros. You can open your .xlsm workbooks, edit your scripts in the built-in VBA Editor, and run advanced AutoFilter tasks without changing a single line of code.

1. Open Your Macro Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the AutoFilter macro.
2. Access Developer Tools: Navigate to the 'Developer' tab located on the top ribbon to access macro-related features.
3. Open the VBA Editor: Click the 'VBA Editor' button to view and modify your AutoFilter code just as you would in Excel.
4. Execute the Macro: Run your macro directly from the editor or trigger it via your assigned worksheet buttons.

Full compatibility with Excel VBA macros and AutoFilter methodsSeamlessly open, edit, and save .xlsx and .xlsm formatsBuilt-in Developer tools and VBA EditorLightweight alternative with a familiar spreadsheet interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does AutoFilter struggle with formula errors in VBA?

VBA evaluates the #N/A result generated by functions like VLOOKUP as an Error data type rather than a standard text string. If the AutoFilter criteria expects text and doesn't exactly match the system's localized error string, the filter will fail to find those rows.

Can I filter out (exclude) #N/A results using VBA?

Yes. To hide the #N/A errors and show all valid data, you can set the criteria to exclude it. Using the direct method, your code would be: .AutoFilter Field:=1, Criteria1:="<>#N/A".

Does the VBA criteria syntax for errors change depending on the Excel language?

Yes. If you are filtering the error directly, non-English versions of Excel may require the localized error string (for example, #N/D in Spanish or #N/B in Dutch). This is why using the IFERROR method to output plain text is generally recommended for shared workbooks.

Excel & Data Problems