Hi
Using only worksheet functions, how do I find out if a row is hidden?
Thanks.

I don't think you can, you need VBA.

This is a UDF that can do it

Function RowIsHidden(rng As Range)
If Rng.Rows.Count = 1Then
RowIsHidden = rng.Row.Hidden
End If
End Function

and use like

=RowIsHidden(H10)

--
__________________________________
HTH

Bob

"Ray" wrote in message
...
Hi
Using only worksheet functions, how do I find out if a row is hidden?
Thanks.

One way would be to have say a series of 1s in a column, eg lets assume
A2:A100, and then in cell A1 you were to use a formula such as

=SUM(A2:A100)-SUBTOTAL(109,A2:A100)

then any value in cell A1 other than 0 would indicate a hidden row within
that range.

You could also conditionally format cell A1 to flag up red or something if
that condition was met.

The 109 argument in the SUBTOTAL function is only available in Excel 2003
onwards.

Regards
Ken..............................



"Ray" wrote in message
...
Hi
Using only worksheet functions, how do I find out if a row is hidden?
Thanks.

Hi,

Andy, Ken, Bob, thank-you for your replies.

The idea of using subtotal() was useful as I can indicate whether I want
hidden values to be included into the function.

Cheers,

Rayney

"Ray" wrote:

Hi
Using only worksheet functions, how do I find out if a row is hidden?
Thanks.