Hide multiple rows on sheet based on cell value

1
votes

I’m trying, with Excel 2013, to hide and unhide rows when a cell is a certain value.

It's a form which should expand based on answers given.

When C16 = YES hide rows 18:22

When C16 = NO hide rows 24:38

When C16 =blank hide rows 18:38

When L43 = YES unhide rows 43:68 (if it’s not yes a zero is displayed)

I have tried 2 methods.

First: Into the worksheet - selected change in the top right dropdown

Private Sub Worksheet_Change(ByVal Target As Range)

    Range("A18:A22").EntireRow.Hidden = (Range("$C$16").Value = "Yes")

    Range("A24:A38").EntireRow.Hidden = (Range("$C$16").Value = "NO")

    Range("A18:A38").EntireRow.Hidden = (Range("$C$16").Value = "")

    Range("A43:A68").EntireRow.Hidden = (Range("$L$43").Value = "0")

End Sub

Second: code from here:

Unhide rows based on cell value

Using both of these methods only one of the changes seems to go ahead. So cell C16 is changed but that means range L43 is ignored

Also when the cell was blank it didn’t change anything. It remained as is and didn’t hide the columns as required.

2
excelvba

2 Answers

3
votes

Your ranges overlap so even if C16 = "Yes" the line C16 = "" will override it and unhide it. L42 is probably also a number where as you're comparing it to a text value try using the following instead. Your code would run on every single change in your sheet as well so have also updated it to only run when either C16 or L43 is changed

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Not Intersect(Target, Union(.Range("C16"), .Range("L43"))) Is Nothing Then
            .Range("A18:A38").EntireRow.Hidden = False
            Select Case LCase(.Range("C16").Value2)
                Case "yes"
                    .Range("A18:A22").EntireRow.Hidden = True
                Case "no"
                    .Range("A24:A38").EntireRow.Hidden = True
                Case Else
                    .Range("A18:A38").EntireRow.Hidden = True
            End Select

            .Range("A43:A68").EntireRow.Hidden = False
            Select Case LCase(.Range("L43").Value2)
                Case "yes"
                    .Range("A43:A68").EntireRow.Hidden = False
                Case Else
                    .Range("A43:A68").EntireRow.Hidden = True
            End Select
        End If
    End With
End Sub

After comments

I'd break this into two from your comments. The first would watch the dropdown and execute on the change of that cell. The second would update using the calculate event. Put these Subs in the sheets where applicable

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Not Intersect(Target, Union(.Range("C16"), .Range("L43"))) Is Nothing Then
            .Range("A18:A38").EntireRow.Hidden = False
            Select Case LCase(.Range("C16").Value2)
                Case "yes"
                    .Range("A18:A22").EntireRow.Hidden = True
                Case "no"
                    .Range("A24:A38").EntireRow.Hidden = True
                Case Else
                    .Range("A18:A38").EntireRow.Hidden = True
            End Select
        End If
    End With
End Sub

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Me
        .Range("A43:A68").EntireRow.Hidden = False
        Select Case LCase(.Range("L43").Value2)
            Case "yes"
                .Range("A43:A68").EntireRow.Hidden = False
            Case Else
                .Range("A43:A68").EntireRow.Hidden = True
        End Select
    End With
    Application.EnableEvents = True
End Sub
2
votes

Try:

With Worksheets("Sheet1")
    .Rows("18:68").EntireRow.Hidden = False
    opt = UCase(.Range("C16").Value)
    Select Case opt
    Case "YES"
        Rows("18:22").EntireRow.Hidden = True
    Case "NO"
        Rows("24:38").EntireRow.Hidden = True
    Case ""
        Rows("18:38").EntireRow.Hidden = True
    Case Else
        MsgBox "Invalid option in cell C16."
    End Select
    If UCase(.Range("L43").Value) = "Yes" Then
        Rows("43:68").EntireRow.Hidden = True
    Else
        MsgBox "Invalid option in cell L43."
    End Select
End With

...though I didn't understand what you wanted to be '0'.