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?

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

Not without VBA.

Mike F
"David Godinger" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Please delete "ANTI-SPAM" from email address
> (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
> Martin King

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" 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.)
>
> Thanks?
>
> --
> Please delete "ANTI-SPAM" from email address (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>

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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>

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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>

Great!

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

Thanks,

Dave

JMB <(E-Mail Removed)> 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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
>> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>>

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

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" wrote:

> Great!
>
> Now, how about hidden columns? The subtotal function doesn't seem to work
> for that.
>
> Thanks,
>
> Dave
>
> JMB <(E-Mail Removed)> 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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
> >> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
> >>
>
> --
> Please delete "ANTI-SPAM" from email address (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>

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

JMB <(E-Mail Removed)> wrote:

>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" wrote:
>
>> Great!
>>
>> Now, how about hidden columns? The subtotal function doesn't seem to work
>> for that.
>>
>> Thanks,
>>
>> Dave
>>
>> JMB <(E-Mail Removed)> 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 <(E-Mail Removed)> 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(INDIRECT("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 (E-Mail Removed)
>> >> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>> >>
>>
>> --
>> Please delete "ANTI-SPAM" from email address (E-Mail Removed)
>> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King
>>

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