Set RNG

[Guest]

This code works to count hidden rows. But how can I set the range when I call
the function rather than have a spectific range in the code itself.

Function CountHidden()
Dim CellCount As Long
CellCount = 0
Set RNG = Range("a1:a9")
For Each Cell In RNG
If Cell.EntireRow.Hidden Then
CellCount = CellCount + 1
End If
Next Cell
CountHidden = CellCount
End Function

 

[Jim Cone]

Howard,

Function CountHidden(ByRef RNG As Excel.Range) As Long
Dim CellCount As Long
Dim rCell As Excel.Range
CellCount = 0
For Each rCell In RNG.Rows
If rCell.Hidden Then
CellCount = CellCount + 1
End If
Next rCell
CountHidden = CellCount
End Function
'--
Sub FeedThatFunction()
Dim x As Long
x = CountHidden(Selection)
MsgBox x & " hidden "
End Sub

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Howard" <[email protected]>
wrote in message
This code works to count hidden rows. But how can I set the range when I call
the function rather than have a spectific range in the code itself.

Function CountHidden()
Dim CellCount As Long
CellCount = 0
Set RNG = Range("a1:a9")
For Each Cell In RNG
If Cell.EntireRow.Hidden Then
CellCount = CellCount + 1
End If
Next Cell
CountHidden = CellCount
End Function

 

[Guest]

Jim,

Thanks very much!