Testing for hidden rows

[Otto Moehrbach]

Excel 2002, WinXP
I have a range of rows, say Rows("1:10").
These rows may ALL be hidden or none be hidden or some be hidden.
I need to check, via VBA, if ALL the rows are hidden.
The statement:
If Rows("5:10").EntireRow.Hidden Then
returns True if any one or more of the rows are hidden.
I need to know if they are ALL hidden or not.
The number of rows is fixed so I can count the number of hidden rows, if I
knew how.
How can I determine if ALL the rows are hidden?
Thanks for your help. Otto

 

[Bernie Deitrick]

Otto,

Sub TryNow()
Dim i As Integer

For i = 1 To 10
If Not Rows(i).Hidden Then
MsgBox "Not all rows are Hidden."
Exit Sub
End If
Next i

MsgBox "All rows are Hidden."

End Sub

HTH,
Bernie
MS Excel MVP

 

[Otto Moehrbach]

Thanks Bernie, I was afraid it would take a row-by-row check like that.
Otto

 

[Bernie Deitrick]

Otto,

You don't _have_ to do it row by row. You can use the error produced by
looking for visiblecells when all rows are hidden:


Dim i As Integer
On Error GoTo AllHidden
i = Range("1:10").SpecialCells(xlCellTypeVisible).Count
MsgBox "Some or all rows weren't hidden"
Exit Sub
AllHidden:
MsgBox "All rows were hidden"

HTH,
Bernie
MS Excel MVP