Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
Not without VBA.
Mike F "David Godinger" wrote in message ... 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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
Great!
Now, how about hidden columns? The subtotal function doesn't seem to work for that. Thanks, Dave JMB 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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect Hidden Rows by Formula
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 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 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 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(INDIREC T("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 David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King -- Please delete "ANTI-SPAM" from email address David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula or Code to keep Hidden Rows Hidden | Excel Worksheet Functions | |||
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 |