Display Row based on Drop down selection

[fahadk87]

Hello Folks,

I'm new to this community and to Excel coding.

Need some urgent help.

I'm trying to show specific row based on a drop-down list. Below are the methods I've tried so far but no luck, really appreciate if someone can help!

Code 1: Giving Error - Compile Error - Syntax Error
---------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> “$B$2" Then Exit Sub
Select Case Target.Value
Case “ENTP"
Cells.Rows.Hidden = FalseCase “ADV”
Cells.Rows.Hidden = False
Rows("9,11:16,6:27").EntireRow.Hidden = False
Rows("10,17:18,20,22:24").EntireRow.Hidden = True
Case “STND"
Cells.Rows.Hidden = False
Rows("9,13:14,26").EntireRow.Hidden = False
Rows("10:12,15:18,20,22:24,27").EntireRow.Hidden = True
Case “LIN"
Cells.Rows.Hidden = False
Rows("10,14,26").EntireRow.Hidden = False
Rows("9,11:13,15:18,20,22:24,27").EntireRow.Hidden = True
End Select
End Sub

=================================================

Code 2: - No output
-------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range


Set Triggercell = Range("B2")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
If Triggercell.Value = "ENTP" Then
Rows("9:27").EntireRow.Hidden = False
ElseIf Triggercell.Value = ADV Then
Rows("9:9,11:16,6:27").EntireRow.Hidden = False
Rows("10:10,17:18,20:20,22:24").EntireRow.Hidden = True
ElseIf Triggercell.Value = STND Then
Rows("9:9,13:14,26:26").EntireRow.Hidden = False
Rows("10:12,15:18,20:20,22:24,27:27").EntireRow.Hidden = True
ElseIf Triggercell.Value = LIN Then
Rows("10:10,14:14,26:26").EntireRow.Hidden = False
Rows("9:9,11:13,15:18,20:20,22:24,27:27").EntireRow.Hidden = True
End If
End If
End Sub

===================================

Thanks

 

[Yongle]

Try this instead of your FIRST macro

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myStr As String, r
    If Target.Address <> "$B$2" Then Exit Sub

    Cells.Rows.Hidden = False      [COLOR=#006400][I] ' applies to all cases[/I][/COLOR]
    Select Case Target.Value
        Case "ENTP"
            'all rows visible
        Case "ADV"
            myStr = "10,17:18,20,22:24"
        Case "STND"
            myStr = "10:12,15:18,20,22:24,27"
        Case "LIN"
            myStr = "9,11:13,15:18,20,22:24,27"
    End Select
        For Each r In Split(myStr, ",")
            Rows(r).Hidden = True
        Next
End Sub