Again, assuming there are no empty cells in your data.

"David Godinger" wrote:

Hmmmm....thanks to JMB for leading me to a solution.

I don't understand everything he said, but I tested the following formula
in Excel 2003. It's a little crude, but it does the job for me:

=SUBTOTAL(3,A2:A5)-SUBTOTAL(103,A2:A5)

Assuming that every cell in A2 to A5 has a value in it.

If no rows are hidden, the formula gives me a 0.

Otherwise, I get a value for the number of rows that are hidden (2 if I
hid 2 rows).

The function 3 uses COUNTA to include hidden values.

The function 103 uses COUNTA to ignore hidden values.

Any improvements for this formula?

Thanks!

Dave

JMB wrote:

Depending on what version of XL you have and the details of how the cells get
hidden, maybe (using formula from Laurent Longre).

If the cells are hidden due to a filter, and assuming your range does not
contain cells that are empty (but a formula that returns "" should be okay)
you could try

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT("1: "&ROWS(A1:A13)))-1,0)))<ROWS(A1:A13)

should return True if cells are hidden, false if not. Assumes data is in
A1:A13.

In XL 2003, I believe subtotal function was modified to recognize cells that
were hidden by the user by using ?103? for the first argument (I don't have
XL2003 so I could be wrong - check XL help if you have XL 2003).

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(INDIRECT(" 1:"&ROWS(A1:A13)))-1,0)))<ROWS(A1:A13)

If you need to test for either situation (ie cells hidden by the user or as
a result of a filter), you may need to use both option 3 and 103 (assuming
103 only counts cells that are hidden, but not hidden by a filter).

=(SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(INDIRECT( "1:"&ROWS(A1:A13)))-1,0)))+SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(INDIREC T("1:"&ROWS(A1:A13)))-1,0))))<ROWS(A1:A13)


"David Godinger" wrote:

Is there a formula that will notify me if any row in a range is hidden? (I
don't want to do this by VBA.)

I want it to notify me by formula, if that's possible, because I want to
know immediately if something is hidden. (Once I can understand how to
detect a hidden row, I'll probably use conditional formatting to make the
cell turn red to warn me.)

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King