Detect Hidden Columns by Formula

[David Godinger]

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

 

[Tom Ogilvy]

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)

 

[Héctor Miguel]

hi, David !

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 todetect a hidden column, I'll probably use conditional formatting to makethe cell turn red to warn me.)...

it's just a wild idea... if you can use a 'helper row', let's say: row 1 ?

[A1] =cell("width",a1) <- copy this formula up to cell 'G1'

now, use in the 'warning-cell'... -> =countif(a1:g1,"<1")

one 'condition'... a re-calculation of your model is needed in order to update the 'warning-cell' :-(

you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???

as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns

hth,
hector.

 

[David Godinger]

Thanks, but I have trouble following the language. How would a sample
formula look?

 

[David Godinger]

Hi Héctor,

The formula does what you said. Too bad a recalulation is required.

By the way, I didn't understand what you said about using conditional
format to hide the row. And what is "bth" at the end?

Thanks!

Dave

hi, David !

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 todetect a hidden column, I'll probably use conditional formatting to makethe cell turn red to warn me.)...

it's just a wild idea... if you can use a 'helper row', let's say: row 1 ?

[A1] =cell("width",a1) <- copy this formula up to cell 'G1'

now, use in the 'warning-cell'... -> =countif(a1:g1,"<1")

one 'condition'... a re-calculation of your model is needed in order to update the 'warning-cell' :-(

you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???
Huh?

as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns

What's an "almost-hidden" column?

 

[Héctor Miguel]

hi, David !

The formula does what you said. Too bad a recalulation is required.
By the way, I didn't understand what you said about using conditional format to hide the row.
And what is "bth" at the end?

I'm sorry, my mistake, I didn't want you to understand as really hiding the entire row
only the content of the cells with the function cell(...)

hth is an acronym of Hope This Help
BTW would be it of By the way...

columns -almost- hide has been because the function cell ("width") returns the width of the column in points rounded to integer
that is to say, if you adjust the width of a column i.e. to 0.5 it would be detecting by <1 as if really were a 'hidden' column

regards,
hector.

__ previous posts __

you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???
Huh?

as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns

What's an "almost-hidden" column?