Hide/Unhide Rows in other sheets based on selection from 3way drop down list

[jacksonattwood]

Hi,

I am working on a document where based on the selection in a cell ("$F$6") of No, EWP or AusPost different rows in corresponding worksheets hide/unhide. 

The issue I am having is:

if I select "No" in ("$F$6") it hides everything I require hidden (no issue)

if I select either "EWP" or "AusPost" it unhides the correct rows (no issue)

if I select either "EWP" or "AusPost" after having already selected the opposite value "EWP" or "AusPost" it will unhide the correct rows but not rehide the others.

see code and screenshot of code.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "No": Worksheets("Contractors").Range("15:16,39:44").EntireRow.Hidden = True
Case Is = "EWP": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
Case Is = "EWP": Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "No": Worksheets("Consultants").Range("18:21:22:24").EntireRow.Hidden = True
Case Is = "EWP": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
Case Is = "EWP": Worksheets("Consultants").Range("22:24").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Consultants").Range("22:24").EntireRow.Hidden = False
Case Is = "AusPost": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True
End Select
End If

End Sub

' Contractors Worksheet - 42:44 = AusPost specific Lines
' Consultants Worksheet - 22:24 = AusPost specific Lines
' Contractors Worksheet - 15,39:41 = EWP specific Lines
' Consultants Worksheet - 18:21 = EWP specific Lines

[#2]

This was the original code in the document when it was a "yes",  "no" or  "blank"  dropdown list and it  would correctly unhide/hide the rows.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "No": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Case Is = "Yes": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "No": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True
Case Is = "Yes": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
End Select
End If

End Sub

[#3]

@jacksonattwood Combine all the hide and unhide actions into one Case statement, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No":
Worksheets("Contractors").Range("15:16,39:44").EntireRow.Hidden = True
Worksheets("Consultants").Range("18:24").EntireRow.Hidden = True

Case Is = "EWP":
Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = True
Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
Worksheets("Consultants").Range("22:24").EntireRow.Hidden = True

Case Is = "AusPost":
Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = False
Worksheets("Consultants").Range("22:24").EntireRow.Hidden = False
Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True

End Select
End If

End Sub

Tested it and it seems to work as intended.