VBA - Hide rows based on drop down answer

AndyMiller

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
Try
VBA Code:
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

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
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

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
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
Untitled.png
 
Last edited:

AndyMiller

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,322
Messages
5,929,289
Members
436,660
Latest member
Mouseinalabyrinth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top