Detect Hidden Rows by Formula

[David Godinger]

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.)

Thanks?

 

[Mike Fogleman]

Not without VBA.

Mike F

 

[Guest]

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(INDIRECT("1:"&ROWS(A1:A13)))-1,0))))<>ROWS(A1:A13)

 

[David Godinger]

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

 

[Guest]

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).

 

[Guest]

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

 

[David Godinger]

Great!

Now, how about hidden columns? The subtotal function doesn't seem to work
for that.

Thanks,

Dave

 

[Guest]

If your range was A2:F2, I expect it would be:

=2*COLUMNS(A2:F2)-(SUBTOTAL(3,A2:F2)+SUBTOTAL(103,A2:F2))

 

[David Godinger]

Thanks for the attempt, but I tried that already and it doesn't work. The
result of the formula is 0, whether or not any of the columns A-F are
hidden. (All the cells have values.)

Best,

Dave

If your range was A2:F2, I expect it would be:

=2*COLUMNS(A2:F2)-(SUBTOTAL(3,A2:F2)+SUBTOTAL(103,A2:F2))