VBA & Macro Problems

How to Use Excel VBA to Hide Rows Based on a Drop-Down Selection

Maira Mehtab Sep 24, 2026 870 views

Question details

The user needs to automatically hide or unhide specific groups of rows in a worksheet depending on the value selected from a drop-down list in a target cell.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating row visibility based on a user's selection from a data validation drop-down list. In this case, hiding or unhiding rows 17-42 based on selecting 'Blank', 'Month(s)', or 'Weeks' in cell J15.
Observed behavior: Dynamic structural changes like row visibility cannot be triggered by standard formulas, so a VBA Worksheet_Change event is required to evaluate the cell and modify the row properties.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the VBA code can execute successfully.

Solution 1Recommended

Use a Worksheet_Change Event Macro

Add a VBA script to the specific worksheet module that triggers automatically whenever the designated cell's value changes.

This method uses the Worksheet_Change event to monitor the target cell (J15). When the drop-down value changes, the code first hides all potential target rows to reset the view. It then uses a Select Case statement to unhide only the rows relevant to the chosen value.

Open the VBA Editor

Right-click the specific worksheet tab at the bottom of your Excel window (where the drop-down is located) and select 'View Code' from the context menu.

Insert the VBA Code

In the code window that appears, copy and paste the following VBA code: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("J15")) Is Nothing Then Exit Sub Rows("17:42").Hidden = True Select Case Target.Value Case "Weeks" Rows("31:42").Hidden = False Case "Month(s)" Rows("17:28").Hidden = False End Select End Sub

Test the Drop-Down Selection

Close the VBA Editor. Go back to your worksheet and change the value in cell J15 using the drop-down menu. The specified rows will now hide and unhide automatically according to your selection.

Customizing the VBA Code: If your drop-down list is in a different cell, update the 'Range("J15")' reference. Similarly, update the 'Rows("...")' arguments if you need to hide different row ranges.

Automate Your Spreadsheets Seamlessly with WPS Office

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to run Worksheet_Change events and automate tasks like hiding rows based on drop-down selections exactly as you would in Microsoft Excel.

1. Open your Workbook in WPS Office: Launch WPS Office Spreadsheet and open your macro-enabled workbook.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'VB Editor'.
3. Apply the Macro: In the Project window, double-click the relevant worksheet, paste your Worksheet_Change code, and save your progress.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsSeamlessly execute VBA codes and automated sheet eventsLightweight application with fast performance and reliable calculationsFamiliar developer tools and VB Editor interface for easy management

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Worksheet_Change VBA code running when I select an item from the drop-down?

This usually happens for two reasons: macros are disabled in your security settings, or the code was placed in a standard module (like Module1) instead of the specific Sheet module. Ensure you right-clicked the exact worksheet tab and chose 'View Code' to place the script.

Can I hide rows based on a cell value using formulas without VBA?

No, standard Excel formulas only return values and cannot perform structural actions like altering row or column visibility. To hide rows automatically based on cell changes, you must use VBA.

How do I create the drop-down list in cell J15?

Select cell J15, go to the 'Data' tab, click 'Data Validation', choose 'List' under the Allow criteria, and enter your items separated by commas in the Source box (e.g., Blank, Month(s), Weeks).

Will this code cause an error if I delete multiple cells at once?

It might, because deleting multiple cells changes a range with a count greater than 1, which the Target.Value evaluation might struggle to handle. You can prevent this by adding the line 'If Target.Count > 1 Then Exit Sub' right after the Intersect line in your VBA code.

Excel & Data Problems