VBA & Macro Problems

How to Fix Excel VBA AutoFilter Using Field Number as Criteria

Steve K Nov 2, 2026 869 views

Question details

The user needs to resolve an issue where an Excel VBA AutoFilter macro filters using a column number instead of the actual cell value when triggered via a worksheet event or button click.

How to Fix Excel VBA AutoFilter Using the Field Number as Criteria

Product: Excel
Device & OS: not provided
Scenario: Running a VBA macro to filter data based on a specific keyword located in a designated column.
Observed behavior: The macro filters correctly when run via shortcut (Ctrl+I) or the macro dialog, but incorrectly uses the field or column number as the criteria when triggered by clicking a cell or button, resulting in zero returned rows.

Before you start

Ensure you have saved a backup of your Excel workbook before modifying the VBA code to prevent accidental data loss or code corruption.

Solution 1Recommended

Use Fully Qualified Range References Instead of ActiveCell

Modify the VBA code to use explicit worksheet and row references instead of relying on ActiveCell or ActiveRow, which can change depending on how the macro is triggered.

When a macro is executed via a button click or a worksheet event, the active selection often shifts away from your target data row. If your VBA script relies on ActiveCell to define the AutoFilter criteria, it will pull incorrect values (such as the column index) because the focus has moved. Fully qualifying your references ensures the macro targets the exact row and column required regardless of how it is launched.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and locate the module containing your AutoFilter macro.

Identify Unqualified References

Look for instances of 'ActiveCell', 'ActiveRow', or 'Selection' within the lines defining your AutoFilter criteria.

Define the Target Row Variable

Declare a variable to securely hold the intended row number. If using a button, you might capture the row with: targetRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row.

Update the Criteria Expression

Replace the unqualified reference with a fully qualified one. For example, explicitly point to column CJ and the target row by updating the code to: Criteria1:=ws.Range("CJ" & targetRow).Value.

Save and Test

Save your VBA project, close the editor, and click the trigger button in your worksheet to verify that the AutoFilter now applies the correct criteria.

Use Fully Qualified Range References Instead of ActiveCell

Best Practice: Always qualify your ranges with a specific worksheet object (e.g., ws.Range instead of just Range) to prevent the macro from accidentally executing on the wrong active sheet.

Run VBA Macros Seamlessly

Edit and Run Your VBA Macros in WPS Spreadsheets

WPS Office offers robust, built-in support for VBA macros. You can edit your macro code directly within WPS Spreadsheets to fix AutoFilter issues, utilizing the same familiar VBA syntax to automate your workflow without errors.

1. Open your Workbook in WPS: Launch WPS Spreadsheets and open your macro-enabled workbook (.xlsm).
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on Visual Basic to launch the integrated VBA editor.
3. Modify the AutoFilter Code: Locate your AutoFilter macro in the project explorer and update the Criteria1 argument to use a fully qualified range reference.
4. Run and Test: Save the changes, exit the editor, and trigger your macro via your assigned button to test the corrected AutoFilter behavior.

Fully compatible with Microsoft Excel VBA syntax and macrosBuilt-in VBA code editor with comprehensive debugging toolsFree and lightweight alternative for powerful spreadsheet automationSeamlessly supports advanced AutoFilter and data processing scripts

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA AutoFilter work from the macro menu but fail from a button?

Running a macro from a button can change the active selection. If your code uses ActiveCell to determine the filter criteria, clicking the button shifts the focus to the button itself, causing the code to pick up the wrong value.

How do I correctly pass a variable as an AutoFilter criterion in VBA?

Store the desired value in a variable before applying the filter, and ensure you explicitly reference the worksheet. For example, declare a variable, assign it the specific cell value like myCriteria = ws.Range("A2").Value, and then use Criteria1:=myCriteria in your AutoFilter method.

Can I edit and run Excel VBA macros in WPS Office?

Yes, WPS Office provides excellent compatibility with Excel VBA macros. You can open your existing macro-enabled workbooks and edit the code using the built-in VBA Editor available in WPS Spreadsheets.

What does the Criteria1 parameter do in Excel VBA AutoFilter?

The Criteria1 parameter specifies the primary condition or value that the AutoFilter will use to filter the data in a specific column. It can accept exact strings, numerical values, wildcards, or dynamically assigned variables.

Excel & Data Problems