Excel VBA: Hide rows based on a formula; erroring on cell being blank

by

Here’s the code I put in.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Activate
    If Not Application.Intersect(Range("G17"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Yes": Rows("21:27").EntireRow.Hidden = False
        Case Is <> "Yes": Rows("21:27").EntireRow.Hidden = True
        Case Is = "": Rows("21:27").EntireRow.Hidden = True
    
        End Select
    End If
    End Sub

What I was expecting was that if Cell G17 says anything other than “Yes”, rows 21-27 will be hidden.

It works unless I try and delete the contents of the cell. If I delete the contents of the cell, I get a Run-time error ’13’: Type mismatch.

Help!

  • Select Case Target.Value —> Select Case Me.Range("G17").Value?

    – 




  • Btw your third condition is redundant.

    – 

  • Activesheet is activated, ActiveSheet.Activate is useless

  • Validate the changed cell with Target.Address = "$G$17"

Please try.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$17" Then
        Rows("21:27").EntireRow.Hidden = Not (Target.Value = "Yes")
    End If
End Sub

Leave a Comment