Excel VBA: Show or Hide Rows Based on Yes or No Values
Question details
Automatically show or hide specified Excel row ranges based on Yes or No selections in cells D2:D7.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses cells D2:D7 as controls for different groups of rows.
- Observed behavior
- Changing a control cell should update its associated row range according to the selected value.
Open the workbook in desktop Excel and identify the worksheet containing D2:D7. The example below treats Yes as show and No or blank as hide; adjust the row mappings if your layout differs.
Use a Worksheet_Change macro to control mapped row ranges
Use this approach when each cell in D2:D7 controls a separate group of rows and Yes should show that group.
The example maps D2 to rows 14:17, D3 to 18:23, D4 to 24:28, D5 to 29:34, D6 to 35:39, and D7 to 40:43. No, blank, error, and other values hide the associated rows.
In desktop Excel, open Developer > Visual Basic. In the Project pane, double-click the worksheet that contains D2:D7. If Developer is not visible, enable it through Excel's ribbon customization settings.
Paste this code into the worksheet module. If the module already has a Worksheet_Change procedure, merge the logic into the existing procedure instead of adding a second one: Private Sub Worksheet_Change(ByVal Target As Range) Dim groups As Variant Dim i As Long Dim controlCell As Range If Intersect(Target, Me.Range("D2:D7")) Is Nothing Then Exit Sub groups = Array("14:17", "18:23", "24:28", "29:34", "35:39", "40:43") For i = 0 To 5 Set controlCell = Me.Range("D2").Offset(i, 0) If Not Intersect(Target, controlCell) Is Nothing Then If IsError(controlCell.Value2) Then Me.Rows(groups(i)).Hidden = True Else Me.Rows(groups(i)).Hidden = _ (LCase$(Trim$(CStr(controlCell.Value2))) <> "yes") End If End If Next i End Sub
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted and the file is trusted, then change D2 to Yes and No to check rows 14:17. Repeat for D3:D7 and their mapped ranges.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets as you move between office suites. VBA support and macro behavior can vary, so verify your workbook before relying on its automation.

Frequently Asked Questions
How do I make Yes hide rows instead of showing them?
In the worksheet's VBA module, change the comparison from <> "yes" to = "yes". With that change, Yes hides the mapped rows and other non-error values show them; update the error branch if you want a different error behavior.
Why doesn't the macro run when I change D2?
Check that the code is in the module for the worksheet containing D2, not a standard module. Save the file as .xlsm and enable macros for the trusted workbook when prompted.
Will the macro work when D2 contains a formula?
Worksheet_Change does not run when a formula result changes through recalculation. Formula-driven controls need a different approach, such as a Worksheet_Calculate procedure.
Can I use this macro in Excel for the web?
Excel for the web cannot run VBA macros. Open the workbook in desktop Excel to run and test the Worksheet_Change procedure.




