Hide and Unhide Specific Blank Rows With the Same Button

Question

Is there a way to modify this code so that it only hides/unhide rows from a specific range such as Row 21:89?

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

What if UsedRange is up till 1048576 rows? And hence, if you want to find the last row then use THIS method. Now back to your question. ...specific range such as Row 21:89 Here you already have the start row and the end row so why not use that in the for loop? — Siddharth Rout

PermaNoob PermaNoob · Accepted Answer · 2013-11-19T22:59:08

You could use something like this:

hStatus = Rows("21:89").Hidden
If hStatus = False Then hStatus = True Else hStatus = False
Rows("21:89").Hidden = hStatus

You need to check what the hidden property is, set it to a variable, then switch the variable to the opposite and use that to set your hidden property.

Hide and Unhide Specific Blank Rows With the Same Button

1 Answers