VBA & Macro Problems

How to Use VBA to Hide Excel Columns Based on Cell Values

John Wilson Oct 9, 2026 869 views

Question details

The user needs to automatically hide or unhide specific columns in an Excel worksheet depending on the value selected in a specific column, such as a drop-down list in column A.

How to Automatically Hide Excel Columns Based on Cell Values Using VBA

Product: Excel
Device & OS: not provided
Scenario: Hiding irrelevant data columns dynamically when a specific name or option is selected from a cell drop-down, streamlining the view for specific users.
Observed behavior: Columns currently remain visible regardless of the selected cell value, requiring manual hiding and unhiding by the user.

Before you start

Ensure that you have enabled the Developer tab in your spreadsheet software and saved your workbook as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run properly.

Solution 1Recommended

Use a Worksheet_Change VBA Macro to Automate Hidden Columns

Implement a dynamic VBA script in the specific worksheet's code module to trigger column visibility changes instantly whenever a cell's value is updated.

This method uses an event handler that listens for changes in a specific range (like Column A). When a matching value is detected, it unhides all columns first, then hides only the columns specified in your script.

Access the Worksheet Code Module

Right-click the specific worksheet tab at the bottom of your Excel window and select 'View Code' from the context menu to open the VBA Editor.

Paste the VBA Macro Code

In the blank module window, paste your Worksheet_Change code. Ensure you include a line like 'Cells.EntireColumn.Hidden = False' to reset visibility before applying new hidden ranges.

Customize the Trigger and Hidden Ranges

Edit the target intersection to match your input column, such as 'If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub'. Then, define your conditions, for example: 'If Target.Value = "Suzanne" Then Range("B:D").EntireColumn.Hidden = True'.

Save as a Macro-Enabled Workbook

Close the VBA Editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format drop-down list to ensure your VBA code is saved securely.

Use a Worksheet_Change VBA Macro to Automate Hidden Columns

Code Efficiency: Adding 'If Target.Cells.Count > 1 Then Exit Sub' and 'If Target.Value = "" Then Exit Sub' at the beginning of your script prevents errors when deleting values or selecting multiple cells at once.

Powerful VBA Support in WPS Spreadsheet

Automate Your Worksheets Seamlessly with WPS Office

WPS Spreadsheet provides comprehensive support for standard Excel macros and VBA scripts, allowing you to run, edit, and save Macro-Enabled Workbooks (.xlsm) without altering your workflow.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic' to launch the VBA Editor.
3. Add or Edit Macros: Double-click your desired Sheet in the Project window and paste or modify your Worksheet_Change code exactly as you would normally.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in Developer tools and Visual Basic editorLightweight, fast, and highly intuitive user interfaceSeamless migration for existing VBA projects

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro triggering when I select a drop-down value?

Ensure that Macros are enabled in your security settings. Also, verify that the code was pasted into the specific Worksheet module (e.g., Sheet1) rather than a standard module (e.g., Module1).

How do I hide multiple non-adjacent columns using this VBA code?

You can specify non-adjacent columns in the range by separating them with a comma. For example, use 'Range("E:G,S:T").EntireColumn.Hidden = True' to hide columns E through G, and S through T simultaneously.

Will this macro work if the cell value is updated by a formula instead of a drop-down?

No, the 'Worksheet_Change' event only triggers upon direct user input or drop-down selection. If a formula calculates and changes the cell value, you will need to use the 'Worksheet_Calculate' event instead.

Excel & Data Problems