Hide and Unhide Blank Rows With the Same Button

Question

I have written the below code to hide blank rows within the used range of a worksheet. The code works perfectly fine. I have assigned this macro to a button on the worksheet. Clicking the button hides the blank rows within the used range.

Question: How do I modify the code so that clicking this same button does the reverse? If the blank rows are unhidden, then clicking the button hides them; and if they are hidden, then clicking the same button unhides them? I want to execute both procedures with one button.

Sub HideLLRows()
'This sub un/hides blank rows in EIRP LL

Application.ScreenUpdating = False

Dim LastRow As Long
Set EIRPLL = Sheets("EIRP LL")

LastRow = EIRPLL.UsedRange.Rows.Count

For i = 6 To LastRow
    If EIRPLL.Range("B" & i) = "" Then
        EIRPLL.Rows(i).Hidden = True
    End If
Next

Application.ScreenUpdating = True
End Sub

chris neilsen chris neilsen · Accepted Answer · 2013-08-25T03:30:26

The simple answer is to toggle the Hidden state of each blank row as you find it

EIRPLL.Rows(i).Hidden = Not EIRPLL.Rows(i).Hidden

This has the disadvantage that if the user changes the hidden state of one or more blank rows then this macro will not get you back to all hidden or all visable.

The alternative is to set the visibility based on the first blank found.

Here's your code refactored, with some additional optimisations:

Dim all your variables
Loop a varaint array, not a range. This is much faster
Set the Hidden property of all rows in one go

Sub HideLLRows()
    'This sub un/hides blank rows in EIRP LL

    Application.ScreenUpdating = False
    Dim i As Long
    Dim EIRPLL As Worksheet
    Dim NewState As VbTriState
    Dim dat As Variant
    Dim rws As Range

    Dim LastRow As Long
    Set EIRPLL = Sheets("EIRP LL")

    With EIRPLL.UsedRange
        LastRow = .Rows.Count - .Row + 1 ' in case used range doesn't start at row 1
        dat = .Columns(2).Resize(LastRow, 1)
    End With

    NewState = vbUseDefault
    With EIRPLL
        For i = 6 To LastRow
            If dat(i, 1) = "" Then
                If NewState = vbUseDefault Then
                    NewState = Not .Rows(i).Hidden
                End If
                If rws Is Nothing Then
                    Set rws = Cells(i, 1)
                Else
                    Set rws = Union(rws, Cells(i, 1))
                End If
            End If
        Next
    End With
    rws.EntireRow.Hidden = NewState

    Application.ScreenUpdating = True
End Sub

Hide and Unhide Blank Rows With the Same Button

1 Answers