VBA & Macro Problems

How to Auto-Filter Excel Rows Based on an 'X' in a Selected Column

Jason MillerJason Miller Nov 3, 2026 868 views

Question details

The user wants to automatically display only the rows containing an 'X' in a dynamically selected column, avoiding the need to manually adjust the drop-down filter for each individual column.

How to Auto-Filter Excel Rows Based on an 'X' in a Selected Column
Product
Excel
Device & OS
not provided
Scenario
Managing a data table containing product names and various attributes, where cells contain either an 'X' or are left blank.
Observed behavior
Manually deselecting blank values in the column filters every time a different attribute needs to be checked is tedious and inefficient.
Before you start

Ensure that the Developer tab is enabled in your spreadsheet ribbon and that your file is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run.

Solution 1Recommended

Use a Worksheet SelectionChange Event Macro

This solution automatically triggers an AutoFilter for the value 'X' whenever you click a specific column header.

By utilizing VBA's Worksheet_SelectionChange event, you can program Excel to recognize when a header cell is clicked. The script will instantly clear previous filters and apply a new filter to show only rows with 'X' in the newly selected column.

1
Open the VBA Editor

Press Alt + F11 to launch the Visual Basic for Applications (VBA) editor.

2
Select the Target Worksheet

In the left-hand Project Explorer pane, double-click the specific worksheet where your data table is located.

3
Insert the Event Code

From the drop-down menus at the top of the code window, choose 'Worksheet' and 'SelectionChange'. Insert code to apply an AutoFilter to your data range with Criteria1:="X" based on Target.Column.

4
Test the Automation

Close the VBA editor and click on any column header in your data table. The rows should automatically filter to display only those with an 'X'.

Use a Worksheet SelectionChange Event Macro
Macro Security: If the code does not run, check your Trust Center settings to ensure macros are enabled for the current document.
Advanced Spreadsheet Tool

Automate Data Filtering Seamlessly in WPS Spreadsheet

WPS Spreadsheet features comprehensive support for VBA macros and Form Controls, allowing you to implement automated 'X' filtering effortlessly. Enjoy a familiar interface to write, edit, and execute your scripts.

  1. 1. Install WPS Office: Download and install the free WPS Office suite on your device.
  2. 2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the data.
  3. 3. Access Developer Tools: Navigate to the Developer tab on the ribbon to manage macros or insert form controls.
  4. 4. Run the Automation: Click your column headers or buttons to seamlessly execute the VBA auto-filter.
Full compatibility with Microsoft Excel VBA macros and .xlsm filesBuilt-in Developer tools for adding Form Control buttons easilyLightweight application that processes large data tables quicklyFree alternative offering professional-grade spreadsheet features
microsoft office alternative - wps office

Frequently Asked Questions

How do I clear the macro filter to view all rows again?

You can clear the filter by navigating to the Data tab and clicking 'Clear'. Alternatively, you can write a separate VBA macro containing the command 'ActiveSheet.ShowAllData' and assign it to a 'Reset' button.

Why is my Worksheet_SelectionChange event not triggering?

This usually happens if Macros are disabled in your spreadsheet security settings or if Excel's Application.EnableEvents property was accidentally set to False. Restarting the application or enabling macros in the Trust Center generally resolves this.

Can I modify the macro to filter for multiple specific characters?

Yes. In your VBA AutoFilter criteria, you can adjust the Criteria1 value. To filter multiple characters, you can set the Operator parameter to xlOr and define Criteria2, or use an Array for more than two variables.