Hide/Show Rows based on Cell Value with Data Validation
 

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

Any help is appreciated - THANKS!

Hide/Show Rows based on Cell Value with Data Validation
 

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)

"Shelly" wrote in message
...
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
----------------

Any help is appreciated - THANKS!

Hide/Show Rows based on Cell Value with Data Validation
 

"Shelly" skrev i en meddelelse
...
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
----------------

Any help is appreciated - THANKS!

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

Hide/Show Rows based on Cell Value with Data Validation
 

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!!


"Per Jessen" wrote:


"Shelly" skrev i en meddelelse
...
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
----------------

Any help is appreciated - THANKS!

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