Posted to microsoft.public.excel.programming
|
|
Detect Hidden Columns by Formula
I would say no using just built in functions (without using values of the
cells - example using nothing like all columns and only columns with 3 in
row 2 are hidden)
--
Regards,
Tom Ogilvy
"David Godinger" wrote in message
...
Hi,
Is there a formula that will notify me if any column(s) in a range are
hidden? (I don't want to do this by VBA.)
I want it to notify me by formula, if that's possible, because I need to
know immediately if something is hidden. (Once I can understand how to
detect a hidden column, I'll probably use conditional formatting to make
the cell turn red to warn me.)
The following formulas worked with rows, when the cells A2-A5 had values
in
them. However, I can't make them work by changing "ROWS" to "COLUMNS" and,
for example, "A2:A5" to "A2:G2".
When using a filter
=2*ROWS(A2:A5)-(SUBTOTAL(3,A2:A5)+SUBTOTAL(103,A2:A5))
Without a filter
=SUBTOTAL(3,A2:A5)-SUBTOTAL(103,A2:A5)
(Credits to JMB for the formulas that worked with rows. See the thread
with
the subject. "Detect Hidden Rows by Formula.")
Thanks,
Dave
--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
Martin King
|