Hiding rows based on cell value - VBA

[sloan85]

I am trying to make a form on Excel which consists of various drop down boxes and I am hiding rows based on the content of the drop down. I am completely new to VBA and have found the below online which is working for hiding rows or ranges of rows based on a cell value for "yes" or "no" but I need to hide groups of rows based on several outcomes of the drop down box but I cant get it to work.

I have a drop down with 5 options and i need it to work like the below....

• if drop down in C17 shows AAA, hide rows 21 to 31
• if drop down in C17 shows BBB, hide rows 18 to 20 and 24 to 31
• if drop down in C17 shows CCC, hide rows 18 to 23 and 26 to 31
• if drop down in C17 shows DDD, hide rows 18 to 25 and 29 to 31
• if drop down in C17 shows EEE, hide rows 18 to 28

Any suggestions please? Is the below the best way to hide rows? Below is what I have used when only one per cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Rows("7").Hidden = (Target.Value = "yes")
Else
If Target.Address = "$I$13" Then
Rows("14:20").Hidden = (Target.Value = "no")

End If
End Sub


Thanks!

[georgiboy]

Welcome to the forum

Maybe something like the below would be easier to read:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C17")) Is Nothing Then
            Select Case Range("C17").Value
                Case "AAA"
                    Rows("21:31").Hidden = True
                Case "BBB"
                    Rows("18:20").Hidden = True
                    Rows("24:31").Hidden = True
                Case "CCC"
                    Rows("18:23").Hidden = True
                    Rows("26:31").Hidden = True
                Case "DDD"
                    Rows("18:25").Hidden = True
                    Rows("29:31").Hidden = True
                Case "EEE"
                    Rows("18:28").Hidden = True
                Case ""
                    Rows("18:31").Hidden = False
            End Select
        End If
    End If
    
End Sub

Hope this helps