7 thoughts on “Determine If Cell is Hidden in VBA”

1. What about the case where the font colorindex is set to the color of the cell?
   Range(“F3?).font.colorindex and
   range(“F3?).interior.colorindex do not yield like results

2. Mike –

   If you color the font white, the font color index (using the default palette) is 2, and the interior color index is -4142, the default interior color index.

   If you explicitly color the cell interior white, both font and interior have color index of 2.

   I suspect you’re comparing the non default font color and the default interior, and even though they look the same, their indexes are different.

   – Jon

3. I will usually recommend NOT using VBA. First, it often requires making the cell volatile and second, users has to activate macros – which are deactivated per default due to security issues.

   Especially when it comes to just knowing if a cells is hidden or not, don’t use VBA. Use the SUBTOTAL function.
   E.g., let Cell B1 be a non-empty cell. To check wheither B1 is hidden, just use =IF(SUBTOTAL(103, B1)>0, “True, B1 is visible”, “False, B1 is hidden”)

   Only issue is that using the subtotal in a cell that resides inside the table that you are applying the subtotal function on will make Autofilter for that table act weird (it will effectively be unfunctional!). I think that this is resolved by having the subtotal function in the very last column of the table — but i’m not completely sure about this.

   A safe alternative is to make the Subtotal check in a cell outside the table — but this adds a bunch of other complications…

   But yes, it is indeed very strange that MS has not added functions that makes it easier to distinguish between visible and hidden cells…

4. I can’t get this formula to work

   =IF(SUBTOTAL(103, B1)>0, “True, B1 is visible”, “False, B1 is hidden”)

   I have grouped column A and B, and when I click the group – sign to hide them the formula value doesn’t change.

   I’m using Excel 2003

5. Brian: I got the same thing you did. I can’t believe this is true, but from what I can tell, the arguments in the 100s ignore cells hidden by Hide Rows and not by Hide Columns. Could it be that MS thinks people only want to subtotal vertically? Have I missed something here?

6. @Dick and Brian,

   These two items from the Remarks section in the Help file for the SUBTOTAL function seems to cover this, especially the second one…

   * For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal only nonhidden numbers in a list.

   * The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

7. Pingback: Excel Function Friday: Sum Filtered List With SUBTOTAL « Contextures Blog

Posting code? Use <pre> tags for VBA and <code> tags for inline.