show/hide cells based on dropdown menu

Question

Hi all,


bit of an excel novice, I have read a few posts on here about this but I have to be honest I'm lost.


I have a dropdown in cell B1 and has the options "Please Select..." "Single Rate" "Economy 7" "Comfort Plus White" "Comfort Plus Control” and "Off Peak"


What I would like to do is have:


Please Select... hide A4 - A7

Single Rate only show A4

Economy 7 only show A4 & A5


Comfort Plus White only Show A4, A5 & A6


Comfort Plus Control only show A4 & A6

Off Peak to only show A4 & A7

If anyone could help me I would appreciate it

Answer 1

Hi,

note:

replace Please Select..., with Please Select (without dots)

now,

1. right click on sheet tab, select View Code and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [B1]) Is Nothing Then

[A4:A7].EntireRow.Hidden = False

Select Case Target.Value

Case Is = "Please Select"  '1

[A4:A7].EntireRow.Hidden = True

Case Is = "Single Rate" '2

[A5:A7].EntireRow.Hidden = True

Case Is = "Economy 7" '3

[A6:A7].EntireRow.Hidden = True

Case Is = "Comfort Plus White" '4

[A7].EntireRow.Hidden = True

Case Is = "Comfort Plus Control" '5

[A5,A7].EntireRow.Hidden = True

Case Is = "Off Peak" '6

[A5:A6].EntireRow.Hidden = True

End Select

End If

End Sub

2. press Alt+Q to close visual basic
3. save your file as xlsm (macros enabled workbook)

Answer 2

thanks soo much this has helped me alot