how to find out if a row is hidden

[Ray]

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

 

[Bob Phillips]

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)

 

[mikerickson]

the formula =SUBTOTAL(103,2:2) will return 0 if row 2 is hidden or has no elements.
If 2:2 holds data and is visible that formula will return COUNTA(2:2).

 

[Ken Wright]

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]

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