Solved: Row to hide on drop down selection

[Phelony]

Once more I knock on your door seeking VBA based wisdom!

I've got a drop down menu that is supposed to trigger sets of rows to hide when different selections are made.

However, after several attempts, I've made it either get rid of all the rows, not give them back or just stare blankly back at me hoping I'll go away and leave it alone.

From the below, when I select "client" it should hide rows 3,5,7 and when I select "trust" rows 3,5,7 should come back and rows 2,4,6 should hide.

There will be instances when the same row is required for multiple selections in the full version, but for now, could someone please just let me know how I've screwed this up and how I can fix it?

[VBA]Private Sub Worksheet_Change(ByVal Target As range)

If Target.Address = "$A$1" Then
If Target.Value = Client Then
Rows(3).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = True
Rows(7).EntireRow.Hidden = True
Else
Rows(2).EntireRow.Hidden = False
Rows(4).EntireRow.Hidden = False
Rows(6).EntireRow.Hidden = False
End If
If Target.Value = Trust Then
Rows(2).EntireRow.Hidden = True
Rows(4).EntireRow.Hidden = True
Rows(6).EntireRow.Hidden = True
Else

Rows(3).EntireRow.Hidden = False
Rows(5).EntireRow.Hidden = False
Rows(7).EntireRow.Hidden = False
End If
End If
End Sub[/VBA]

Thanks

Phel x

[mdmackillop]

I prefer Select to multiple Ifs
[vba]
Option Compare Text 'Ignore case

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target.Value
Case "Client"
Cells.Rows.Hidden = False
Rows(3).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = True
Rows(7).EntireRow.Hidden = True
Case "Trust"
Cells.Rows.Hidden = False
Rows(2).EntireRow.Hidden = True
Rows(4).EntireRow.Hidden = True
Rows(6).EntireRow.Hidden = True
End Select
End Sub

[/vba]

[p45cal]

a variation on a theme:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("2:2,4:4,6:6").EntireRow.Hidden = Target = "Trust"
Range("3:3,5:5,7:7").EntireRow.Hidden = Target = "Client"
End If
End Sub
[/vba]

[mdmackillop]

Neat, but you forgot the Unhide line

[p45cal]

no I didn't

[mdmackillop]

Even neater then!

[Phelony]

Md, code works amazingly, massive amendments made for the annoying large sheet I've applied it to, but the one line unhide is the genius I was lacking!

P45, can't get yours to work....

All the same, damsel in distress is no longer distressed!

Thank you so much

Phel x

[mdmackillop]

Hi Phelony
re P45's code. It is case specific. Try adding "Option Compare Text" before the Event code.