VBA & Macro Problems

How to Hide Excel Rows Based on Dropdown Values Using VBA

Elise Williams Sep 28, 2026 868 views

Question details

The user needs to automatically hide or show specific row ranges in an Excel spreadsheet when a value is selected from a data-validation dropdown list.

How to Hide Excel Rows Based on Dropdown Values Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating row visibility based on cell value changes using VBA macros.
Observed behavior: The VBA event procedures do not trigger automatically upon dropdown selection because the code is incorrectly placed in a standard VBA module instead of the dedicated worksheet module.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) so your VBA scripts can run properly.

Solution 1Recommended

Move VBA Event Procedures to the Worksheet Module

Event macros must be placed in the specific worksheet object module rather than a standard module to execute automatically.

When working with data-validation dropdowns, standard macros will not trigger automatically upon cell changes. You must use worksheet event procedures like Worksheet_Change, which only function if placed directly inside the specific worksheet's code module.

Open the VBA Editor

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

Locate the Target Worksheet

In the Project Explorer pane on the left side of the screen, find your workbook and double-click the specific Sheet (e.g., Sheet1) that contains your dropdown cells.

Remove Incorrectly Placed Code

If you previously pasted your event procedures into a standard module (like Module1), double-click that module, delete the code, and return to the worksheet module.

Insert Code in the Worksheet Module

Paste your Worksheet_Change and Worksheet_SelectionChange code directly into the blank code window of the selected worksheet, then save the workbook.

Move VBA Event Procedures to the Worksheet Module

Testing the Macro: Return to your Excel sheet and change the value in your dropdown list. The rows should now hide or unhide automatically based on your script's logic.

Solution 2

Configure the Worksheet_Change Event for Target Cells

Structure your VBA code to correctly identify the dropdown cells before executing the row-hiding logic.

Advanced VBA Automation with WPS Office

Run Excel VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to run, edit, and create event procedures like Worksheet_Change without modifying your existing Excel code.

1. Open Your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook (.xlsm).
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon menu.
3. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to open the built-in VBA editor.
4. Manage and Run Macros: Manage your worksheet modules, edit your VBA scripts, and test your dropdown row-hiding macros exactly as you would natively.

Fully compatible with Microsoft Excel .xlsm files and VBA syntax.Intuitive Developer tab for easy access to the macro editor.Lightweight and fast performance when running complex automated scripts.Free basic version available with a highly familiar user interface.

Frequently Asked Questions

Why does my Worksheet_Change macro do nothing when I select a dropdown value?

This typically happens if macros are disabled in your Trust Center settings, or if the VBA code is placed in a Standard Module instead of the specific Worksheet Module. It can also occur if the 'Application.EnableEvents' property was accidentally set to False during previous macro executions.

Can I use VBA to hide rows based on multiple different dropdowns?

Yes. Within a single Worksheet_Change event, you can use multiple 'If Not Intersect' statements or a 'Select Case Target.Address' block to monitor different dropdown cells (such as B141, B143, and B146) and trigger independent row-hiding rules for each cell.

Is Worksheet_SelectionChange required to hide rows on a dropdown selection?

No. Worksheet_SelectionChange triggers whenever you click or highlight a cell, whereas Worksheet_Change triggers when the actual value inside the cell changes. For responding to data-validation dropdown selections, Worksheet_Change is the proper event to utilize.

Excel & Data Problems