Posted to microsoft.public.excel.programming
|
|
Detect Hidden Rows by Formula
Great!
Now, how about hidden columns? The subtotal function doesn't seem to work
for that.
Thanks,
Dave
JMB wrote:
Sorry, I made that a lot harder than it needed to be. I'm used to seeing
that formula in a multiple condition test involving filtered data and
overlooked a simpler solution.
Subtotal(3,..) and Subtotal(103,..) should count rows that are not hidden.
To get the number of hidden rows, I think you need
=2*ROWS(A2:A5)-(SUBTOTAL(3,A2:A5)+SUBTOTAL(103,A2:A5))
I suspect your formula is working because no rows are hidden by a filter so
Subtotal(3,A2:A5) is returning the same value that Rows(A2:A5) would.
Subtotal(3, A2:A5) returns the number of rows that are visible after a
filter is applied (such as AutoFilter). Subtotal(103, A2:A5) should, as I
understand it, return the number of rows not hidden by the user
(Format/Rows/Hide).
"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
--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
|