Drop Down List - to auto Hide rows

[jbodel]

I have created a Price List for my company that contains 5 different options. Depending on which list they select (from a drop down), the price for services change. However one of the options they can select from the list, has additional services that are listed. I am wondering if there is a way to hide/display rows depending on what list is selected. The list that the select is on a different worksheet than where all the prices are located. I have all the services listed on the 2nd sheet, but I want rows to hide or display depending on what is selected from the drop down?

This would make many members of my company happy if this can be done.

 

[kevin9999]

This is very possible using VBA, but you'd need to provide a lot more detail to elicit usable responses: what are the sheet names; where is the drop down cell; what are the drop down options & which rows do you want hidden depending on which option is selected?

 

[jbodel]

This is very possible using VBA, but you'd need to provide a lot more detail to elicit usable responses: what are the sheet names; where is the drop down cell; what are the drop down options & which rows do you want hidden depending on which option is selected?

My apologies for the delayed response.

The Tab that the drop down is located is called Analysis. It is contained in cells F2. There are a number of options to select, but there are only two that would require a cell to be displayed. If they select option 288 from the drop down, row 35 on the Pro-Forma tab will be unhidden/Visible. If they select 289, row 36 will be visible. In all cases all other rows are visible. It will just have two hidden rows on the Pro-Forma tab - Rows 35 and 36. Then if one of those options are selected then one of them will be displayed.

Again sorry for the delayed response. Hope this answers your questions.

 

[kevin9999]

Please try the following on a copy of your workbook. Place the code in the worksheet code area for the sheet "Analysis" - right click the tab name, select View Code and put the code in the blank code area to the right of the screen. Save the file as macro-enabled or binary.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2")) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Sheets("Pro-Forma").Range("35:36").EntireRow.Hidden = True
        Select Case Target.Value
            Case Is = 288
                Sheets("Pro-Forma").Rows("35").EntireRow.Hidden = False
            Case Is = 289
                Sheets("Pro-Forma").Rows("36").EntireRow.Hidden = False
        End Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

result examples:

hide rows.xlsm
	F
1
2	287
Analysis

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$H$2:$H$5

hide rows.xlsm
	A	B
33	Row 33
34	Row 34
37	Row 37
38	Row 38
Pro-Forma

hide rows.xlsm
	F
1
2	288
Analysis

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$H$2:$H$5

hide rows.xlsm
	A	B
33	Row 33
34	Row 34
35	Row 35
37	Row 37
38	Row 38
Pro-Forma

hide rows.xlsm
	F
1
2	289
Analysis

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$H$2:$H$5

hide rows.xlsm
	A	B
33	Row 33
34	Row 34
36	Row 36
37	Row 37
38	Row 38
Pro-Forma