Need help with a Worksheet_Change sub (hide/unhide rows based on cell value)

[happyfingers]

Very simple piece of code that hides or unhides specific rows based on the value in specific cells.

The first set looks at the value in cell O19 and then hides and unhides specific rows (in 24:28) based on whether cell O19 has values: "", "ONE", "TWO", "THREE" or "FOUR". If none are true then it unhides all rows 24:28.

The second set looks at the value in cell O47 and then hides and unhides specific rows (in 52:56) based on whether cell O47 has values: "", "ONE", "TWO", "THREE" or "FOUR". If none are true then it unhides all rows 52:56.


The problem is that the first set works fine (i.e. when I change the value in O19).

The second set is not working (i.e. when I change the value in cell O47, nothing happens).

Both sets of code need to work independent of each other (if that makes sense).

Can anyone help?

The code is as follows...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'HIDES CERTAIN ROWS BASED ON VALUE IN CELL O19

    If Range("O19").Value = "" Then
        Rows("24:24").EntireRow.Hidden = True
        Rows("26:28").EntireRow.Hidden = False
    Else
    If Range("O19").Value = "ONE" Then
        Rows("25:25").EntireRow.Hidden = True
        Rows("24:24").EntireRow.Hidden = True
        Rows("26:28").EntireRow.Hidden = False
    Else
    If Range("O19").Value = "TWO" Then
        Rows("24:24").EntireRow.Hidden = False
        Rows("26:28").EntireRow.Hidden = True
        Rows("25:25").EntireRow.Hidden = False
    Else
    If Range("O19").Value = "THREE" Then
        Rows("24:24").EntireRow.Hidden = False
        Rows("25:28").EntireRow.Hidden = True
        Else
    If Range("O19").Value = "FOUR" Then
        Rows("24:24").EntireRow.Hidden = False
        Rows("25:28").EntireRow.Hidden = True
        Else
    Rows("24:28").EntireRow.Hidden = False
    End If: End If: End If: End If: End If:

'HIDES CERTAIN ROWS BASED ON VALUE IN CELL O47
 
    If Range("O47").Value = "" Then
        Rows("52:52").EntireRow.Hidden = True
        Rows("54:56").EntireRow.Hidden = False
    Else
    If Range("O47").Value = "ONE" Then
        Rows("53:53").EntireRow.Hidden = True
        Rows("52:52").EntireRow.Hidden = True 
        Rows("54:56").EntireRow.Hidden = False
    Else
    If Range("O47").Value = "TWO" Then
        Rows("52:52").EntireRow.Hidden = False
        Rows("54:56").EntireRow.Hidden = True
        Rows("53:53").EntireRow.Hidden = False
    Else
    If Range("O47").Value = "THREE" Then
        Rows("52:52").EntireRow.Hidden = False
        Rows("53:56").EntireRow.Hidden = True
        Else
    If Range("O47").Value = "FOUR" Then
        Rows("52:52").EntireRow.Hidden = False
        Rows("53:56").EntireRow.Hidden = True
        Else
    Rows("52:56").EntireRow.Hidden = False
    End If: End If: End If: End If: End If:
    
Application.EnableEvents = True

End Sub

[Winon]

Hello happyfingers,

The Code works for me. The only thing is, how do you intend un-hiding Rows which are hidden with an empty Cell?

I have created a Named Range with Dropdown Validation Boxes in O19 and O47 respectively, and changed the "" value To """".

Please try the attached sample Workbook.

Regards.

[happyfingers]

Thanks Winon. That seems to have done the trick. I really appreciate your response. Very very much appreciated!

[Winon]

Thank you for the feedback, and for adding to my reputation.

Glad I could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.