Drop Down List - to auto Hide rows

jbodel

New Member
Joined
Jun 2, 2014
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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.
 
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?
 
Upvote 0
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.
 
Upvote 0
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:
VBA 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
2287
Analysis
Cells with Data Validation
CellAllowCriteria
F2List=$H$2:$H$5

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


hide rows.xlsm
F
1
2288
Analysis
Cells with Data Validation
CellAllowCriteria
F2List=$H$2:$H$5

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


hide rows.xlsm
F
1
2289
Analysis
Cells with Data Validation
CellAllowCriteria
F2List=$H$2:$H$5

hide rows.xlsm
AB
33Row 33
34Row 34
36Row 36
37Row 37
38Row 38
Pro-Forma
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

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
Back
Top