Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Columns by Formula
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 |
#2
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Columns by Formula
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Columns by Formula
Thanks, but I have trouble following the language. How would a sample
formula look? "Tom Ogilvy" wrote: 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) "David Godinger" wrote in message .. . 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.") -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Columns by Formula
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 "Héctor Miguel" wrote: 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? hth, hector. -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Columns by Formula
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detect Hidden Rows by Formula | Excel Programming | |||
vba to detect if column contains partially hidden text | Excel Programming | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
How to detect if a workbook is hidden through VBA | Excel Programming | |||
How to detect if sheet is hidden? | Excel Worksheet Functions |