hide the entire given rows if a specified cell is blank

Please, check the next event code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$62" Or Target.Address = "$A$82" Then
        Select Case Target.Address
            Case "$A$62"
                If Target.Value = "" Then
                    Range("A56:A61").EntireRow.Hidden = True
                Else
                    Range("A56:A61").EntireRow.Hidden = False
                End If
            Case "$A$82"
                If Target.Value = "" Then
                    Range("A78:A81").EntireRow.Hidden = True
                Else
                    Range("A78:A81").EntireRow.Hidden = False
                End If
        End Select
  End If
End Sub

It will be triggered only if you MANUALLY change the value of one of the two required cells.

If their value is the result of a formula, Worksheet_Calculate event must be used, but in a different way. This event does not have any argument (no Target) and you must check the two cells in discussion and act according to their value, independent if they were changed or not when the Calculate event is triggered. If this is the case, I can post such an event code, too.

Edited:

For the version which does not involve the manual changing of the values, please copy this event code in the sheet code module:

Private Sub Worksheet_Calculate()
    If Me.Range("A62").Value = "" Then
        Me.Range("A56:A61").EntireRow.Hidden = True
    Else
        Me.Range("A56:A61").EntireRow.Hidden = False
    End If
    If Me.Range("A82").Value = "" Then
        Me.Range("A78:A82").EntireRow.Hidden = True
    Else
        Me.Range("A78:A82").EntireRow.Hidden = False
    End If
    'Edited:
    'The part for both analyzed ranges being empty:
    If Me.Range("A62").Value = "" And _
                     Me.Range("A82").Value = "" Then
        'Do here what you need...
    End If
End Sub

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top