Hiding Single and Multiple Rows Based on Cell Selection(s)

[ddocg]

On sheet 1 cell C6 I have a drop down with either Yes or No as available selections. This was created with Data Validation and the list is on sheet 2.

I would like to hide rows 7, 8 and 9 if C6 = No and to show them if C6 = Yes.

I am also using the same Yes\No drop box in C11 which should hide Row 12 if No is selected and unhide if Yes.

Likewise in the following cells

C14 should hide Rows 15 and 16 if No is selected unhide if Yes.

C18 should hide Row 19 if No is selected unhide if Yes.

C21 should hide Row 22 if No is selected unhide if Yes.

I have tried using various bits of vba code I found in searching online but to no avail.

Appreciate if someone could help?

 

[ddocg]

Solution

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim hideRows As Range
   
    ' Check if the change occurred in the specified cells (C6, C11, C14, C18, C21)
    Set cell = Intersect(Target, Range("C6,C11,C14,C18,C21"))
   
    ' Exit if the change is not in the specified cells
    If cell Is Nothing Then Exit Sub
   
    ' Set the range of rows to hide or unhide based on the selected value
    Select Case cell.Value
        Case "No"
            Select Case cell.Address
                Case "$C$6"
                    Set hideRows = Rows("7:9")
                Case "$C$11"
                    Set hideRows = Rows("12:12")
                Case "$C$14"
                    Set hideRows = Rows("15:16")
                Case "$C$18"
                    Set hideRows = Rows("19:19")
                Case "$C$21"
                    Set hideRows = Rows("22:22")
            End Select
            hideRows.Hidden = True
        Case "Yes"
            Select Case cell.Address
                Case "$C$6"
                    Set hideRows = Rows("7:9")
                Case "$C$11"
                    Set hideRows = Rows("12:12")
                Case "$C$14"
                    Set hideRows = Rows("15:16")
                Case "$C$18"
                    Set hideRows = Rows("19:19")
                Case "$C$21"
                    Set hideRows = Rows("22:22")
            End Select
            hideRows.Hidden = False
    End Select
End Sub