0

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

Community
  • 1
  • 1
user2693046
  • 1
  • 1
  • 2
  • What if `UsedRange` is up till `1048576` rows? And hence, if you want to find the last row then use [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) 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 Nov 19 '13 at 22:26

1 Answers1

0

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.

PermaNoob
  • 849
  • 5
  • 17