VBA - Hide rows based on drop down answer

[AndyMiller]

Hi guys

I am creating a questionnaire, and have a populated a lot of questions from rows 32 to 53 in my worksheet. I am trying to run a code that hides certain rows depending on how you answer the drop down. The drop down has the following four options;
1 - Intangible Valuations
2 - Business Valuations
3 - Employee Incentive Schemes
Specific Procedures

I have put this code in however when I select the dropdown nothing happens, I don't get a code error but it also doesn't hide any rows:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 And Target.Row = 16 Then

If Target.Value = "1 - Intangible Valuations" Then
Application.Rows("39:53").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "2 - Business Valuations" Then
Application.Rows("32:39,45:53").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "3 - Employee Incentive Schemes" Then
Application.Rows("32:45").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "Specific Procedures" Then
Application.Rows("32:53").Select
Application.Selection.EntireRow.Hidden = True

End If

End If

End Sub


Any help will be hugely appreciated!

 

[bebo021999]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim HidRng As Range
If Intersect(Target, Range("D16")) Is Nothing Then Exit Sub
Range("32:53").EntireRow.Hidden = False
    Select Case Target.Value
        Case Is = "1 - Intangible Valuations"
            Set HidRng = Range("39:53").EntireRow
        Case Is = "2 - Business Valuations"
            Set HidRng = Range("32:39,45:53").EntireRow
        Case Is = "3 - Employee Incentive Schemes"
            Set HidRng = Range("32:45").EntireRow
        Case Else
            Set HidRng = Range("32:53").EntireRow
    End Select
HidRng.Hidden = True
Range("D16").Select
End Sub

 

[AndyMiller]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim HidRng As Range
If Intersect(Target, Range("D16")) Is Nothing Then Exit Sub
Range("32:53").EntireRow.Hidden = False
    Select Case Target.Value
        Case Is = "1 - Intangible Valuations"
            Set HidRng = Range("39:53").EntireRow
        Case Is = "2 - Business Valuations"
            Set HidRng = Range("32:39,45:53").EntireRow
        Case Is = "3 - Employee Incentive Schemes"
            Set HidRng = Range("32:45").EntireRow
        Case Else
            Set HidRng = Range("32:53").EntireRow
    End Select
HidRng.Hidden = True
Range("D16").Select
End Sub

Thanks for replying.

So I have put that code in too but the same thing happens, i.e. it doesn't do anything! I am quite new to VBA so not sure if I'm missing a step, but when I look at the macros on the excel it doesn't appear there at all - I assume it should do? Perhaps this is my issue!

 

[bebo021999]

1) Make sure this code is placed in right sheet even

2) with this "If Target.Column = 4 And Target.Row = 16 Then"
make sure your dropdown is in cell D16

See below image how my code works

 

[AndyMiller]

I think perhaps it is due to a further issue.
I have realised I am using a drop down to select a different list, which in term generates those 4 separate options via a vlookup (i.e. 20 different items, each one can be categorised into the 4 different lines such as 1 - Intangible....".
Would it be the vlookup that is the issue? That is what is in D16.

My code definitely appears to be in the correct place per VBA, although strangely doesn't appear when I view macros in the worksheet.