+ Reply to Thread
Results 1 to 6 of 6

Detect content in hidden columns

  1. #1
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Detect content in hidden columns

    Let's say you have content in rows 1-3, and a "total" sum in row 4. For optics, you may want to hid rows with zeros. Let's say you accidentally hid row 2, but it actually had data. You can detect if you erroneously hid a row by taking the subtotal of all the rows and comparing it to the actual total (in row 4) because the subtotal function excludes hidden rows.

    My question: is there a way to do this for columns as well? If I have data in columns A:C, and I erroneously hide column C because I thought it was all zeros--is there a way to recognize that it needs to be unhidden?

    Thank you,

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Detect content in hidden columns

    subtotal(9,A1:A4) will also count the hidden row.

    subtotal(109,A1:A4) won't count the hidden row
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Detect content in hidden columns

    That works for hidden rows, but not hidden columns. My question is how can I also recognize hidden columns that have data.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,663

    Re: Detect content in hidden columns

    Actually, SUBTOTAL can't be used across column (i.e. won't detect hidden columns).

    Only way I can think of is to use Cell function. However, this requires that you have some mechanism to trigger recalculation of Cell function. As it does not auto-calculate on status change.
    It will only re-evaluate when you change some cell value (manually, not via calculation).

    See attached sample. Hide some columns, then in O2. Put some value. Or double click on the cell and click out.
    Attached Files Attached Files
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Detect content in hidden columns

    That did the trick!!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,663

    Re: Detect content in hidden columns

    Great! And thanks for the rep

    If you are satisfied with the solution provided, please mark the thread as solved using thread tools found at top of your initial post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] MACRO to Detect Hidden Rows
    By Andrej Sulek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2019, 11:43 AM
  2. [SOLVED] Possible to have a cell detect if there are hidden rows and yield a YES/NO result?
    By melegaunt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-05-2015, 05:00 PM
  3. Scroll bar reveal content of hidden columns
    By JonesyCC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2014, 01:17 PM
  4. Replies: 0
    Last Post: 01-15-2014, 05:17 PM
  5. Replies: 3
    Last Post: 08-08-2006, 08:10 AM
  6. [SOLVED] How to detect if a workbook is hidden through VBA
    By Aaron in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2005, 03:05 PM
  7. How to detect if sheet is hidden?
    By hstijnen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1