VBA & Macro Problems

How to Automatically Hide Excel Columns Based on a Filter Using VBA

Steve K Oct 21, 2026 869 views

Question details

The user wants to automatically hide specific columns in a spreadsheet whenever the selection of a data filter in another column changes.

How to Automatically Hide Excel Columns Based on a Filter

Product: Excel
Device & OS: not provided
Scenario: Managing complex datasets where certain columns become irrelevant and need to be hidden depending on the active filter criteria applied by the user.
Observed behavior: Columns remain visible after filtering data, requiring the user to manually select and hide them instead of triggering an automatic response.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools and macros in your Excel or WPS Office settings.

Solution 1Recommended

Use an Event-Driven VBA Macro Combined with a Helper Formula

Apply a VBA macro that detects filter changes via sheet recalculation and automatically hides specific columns based on the filter criteria.

Excel does not natively trigger a standard 'Worksheet_Change' event when a filter is applied because cell values aren't being directly edited. To work around this, you can use the 'Worksheet_Calculate' event coupled with a SUBTOTAL helper formula that forces the sheet to recalculate whenever the filter changes.

Add a helper formula

In an empty or hidden cell (e.g., Z1), type the formula '=SUBTOTAL(3, A:A)'. This formula counts visible rows and will force the worksheet to recalculate whenever a filter is changed.

Open the VBA Editor

Press 'ALT + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Access the specific worksheet module

In the Project Explorer panel on the left, double-click the name of the worksheet containing your data (e.g., Sheet1) to open its code window.

Insert the Worksheet_Calculate event code

Select 'Worksheet' from the left dropdown above the code window, and 'Calculate' from the right dropdown. Write your logic inside this block to check the active filter criteria and set 'Columns("X:Y").Hidden = True' or 'False' accordingly.

Test with dummy data

Close the VBA editor and test the macro by changing the filter dropdowns. Verify that the target columns dynamically hide or unhide.

Use an Event-Driven VBA Macro Combined with a Helper Formula

Sample Data Recommended: Because VBA macros depend heavily on your specific worksheet layout and filter ranges, it is highly recommended to design and test your macro on a dummy workbook with representative data before applying it to your main file.

Advanced Spreadsheets Made Easy

Automate Your Worksheets Using WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to run event-driven scripts to automatically hide columns based on filters exactly like you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook in the WPS Spreadsheet application.
2. Enable Developer Tools: Go to the 'Developer' tab on the top ribbon and click on 'Macros' or 'VBA Editor' to access the scripting environment.
3. Apply Filter Macros: Insert your VBA macro into the sheet module to auto-hide columns, run your test, and save the file to retain your automated workflow.

Full compatibility with Microsoft Excel macro-enabled (.xlsm) formatsBuilt-in VBA macro support for advanced data automationFree, lightweight, and fast performance for large datasetsFamiliar user interface with no learning curve required

microsoft office alternative - wps office

Frequently Asked Questions

Can I use formulas instead of VBA to hide columns?

No, standard spreadsheet formulas cannot alter the structure or visibility of columns. Formulas can only return values to the cells they occupy. A VBA macro is strictly required to dynamically change column visibility.

Why doesn't the Worksheet_Change event trigger when I apply a filter?

Applying an AutoFilter hides rows but does not change the actual data values within the cells, so it bypasses the standard Worksheet_Change event. Using a Worksheet_Calculate event alongside a SUBTOTAL formula is the standard workaround.

Will these Excel macros work in WPS Office?

Yes, WPS Office Pro and specific versions of WPS Office Free include comprehensive VBA support, meaning event-driven macros designed to hide columns in Excel will run seamlessly in WPS Spreadsheet.

Excel & Data Problems