VBA & Macro Problems

Excel VBA: Show or Hide Rows Based on Yes or No Values

Anum MushtaqAnum Mushtaq Nov 13, 2026 869 views

Question details

Automatically show or hide specified Excel row ranges based on Yes or No selections in cells D2:D7.

Show or Hide Excel Rows Based on Yes or No Values with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the worksheet module

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.

2
Add the event procedure

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

3
Save and test

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.

Use a Worksheet_Change macro to control mapped row ranges
Existing values and formulas: Worksheet_Change runs when cells are edited, including pasted values; it does not run when a formula recalculates. Existing values are not applied until the control cells are changed.
Free Microsoft Office alternative

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.

Review and edit spreadsheet row layouts in WPS SpreadsheetCompatible with Microsoft Excel formats including XLSXFamiliar spreadsheet interface for easier migrationFree and lightweight office suite
microsoft office alternative - wps office

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.