Hide/Show Rows based on Cell Value with Data Validation

[Shelly]

Hello, I've been searching through these forums since last night, but just
can't figure this out. I have a cell with data validation, and 2 values
"Migration" and "Transition".

If the cell (B15) is blank, I want all the rows to be hidden (16:32)
If the cell (B15) equals Migration, I want to hide rows 24:32
If the cell (B15) equals Transition, I want to hide rows 17:24

Below is what I have... but I must be missing something.

----------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Range("B15").Value = "" Then
Range(Rows(16), Rows(32)).EntireRow.Hidden = True
ElseIf Range("B15").Value = "Migration" Then
Range(Rows(17), Rows(24)).EntireRow.Hidden = False:
Range(Rows(25), Rows(32)).EntireRow.Hidden = True
ElseIf Range("B15").Value = "Transition" Then
Range(Rows(17), Rows(24)).EntireRow.Hidden = True:
Range(Rows(25), Rows(32)).EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

This should do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Me.Rows("16:32").Hidden = False
If Target.Value = "" Then
Me.Rows("16:32").Hidden = True
ElseIf Target.Value = "Migration" Then
Me.Rows("24:32").Hidden = True
ElseIf Target.Value = "Transition" Then
Me.Rows("17:24").Hidden = True
End If
Application.ScreenUpdating = True
End Sub

You did put the code in the worksheet code module?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Per Jessen]

Hello, I've been searching through these forums since last night, but just
can't figure this out. I have a cell with data validation, and 2 values
"Migration" and "Transition".

If the cell (B15) is blank, I want all the rows to be hidden (16:32)
If the cell (B15) equals Migration, I want to hide rows 24:32
If the cell (B15) equals Transition, I want to hide rows 17:24

Below is what I have... but I must be missing something.

----------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Range("B15").Value = "" Then
Range(Rows(16), Rows(32)).EntireRow.Hidden = True
ElseIf Range("B15").Value = "Migration" Then
Range(Rows(17), Rows(24)).EntireRow.Hidden = False:
Range(Rows(25), Rows(32)).EntireRow.Hidden = True
ElseIf Range("B15").Value = "Transition" Then
Range(Rows(17), Rows(24)).EntireRow.Hidden = True:
Range(Rows(25), Rows(32)).EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Hi Shelly

I have tried your code and it works fine here, just notice that the
testvalue is case sensitive.

Btw: I would unhide all rows before i tested which rows to hide and then
juste hide whatever is to be hidden.

Rows("16:32").EntireRow.Hidden = False

Regards,

Per

 

[Shelly]

It's working now. The only thing I can think of is that I was trying to do
it while the sheet was in Design Mode. THANKS!!