+ Reply to Thread
Results 1 to 9 of 9

Count only visible cells by cell color

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Question Count only visible cells by cell color

    Hello,

    I have some code that does a great job to count the vbGreen boxes in my spreadsheet. When called it will put a row of totals below the range of cells. The issue is that I need it to act like the subtotal function with a 109 or summing of visible. I will need to filter the entire spreadsheet and it will drop rows out according to category so rather than unnecessarily delete those rows I'd like to make useful the filter then subtotal visible. Here is the current code.

    Please Login or Register  to view this content.
    This is called later in another macro by the code:

    Please Login or Register  to view this content.
    I understand the code color for the interior color is found and counted but I can't find a similar post that might point me to the modification to count only visible. I am imagining another "if visible then" nested inside so I'll be working on the code while waiting for a response by looking through posts but I'm struggling with the search engine a bit. I need to figure out how to search better but wrapping quote around search words doesn't seem to narrow it down within the Forum search functions. That's another problem I'll address later.

    I would really appreciate the help with the modification.

    Thank you greatly in advance.

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

    Re: Count only visible cells by cell color

    You'd use Range.SpecialCells(xlCellTypeVisible).

    So something like...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Count only visible cells by cell color

    CK76,

    Thanks and that is a wonderfully simple fix. Thanks for responding. The fix allowed me to set my filter and then apply it. However, the location of the total at the last row + 2 gets messed up with the filter and the totals end up embedded in the filter. I thought I'd try to locate that in a fixed area not inside the filter range area and the result moved the numbers but didn't come up with the total correctly as if it lost the range of the cells. I thought that was established in the ColorFunction Macro called in the original call and applied range R17C:R[-1]C directly below but apparently not or maybe I'm missing something.

    Please Login or Register  to view this content.
    I rewrote it as

    Please Login or Register  to view this content.
    Where N8:NN8 is where the cells ended up but the totals were either 0's or 1's and came from a row I can't pull from. It appears my Range has altered the result of the ColorFunction but not in a way that I can determine the correction. Does anything stand out readily? This seems like the best solution if I can get it to work or get help to make it work.

    Thanks!!

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

    Re: Count only visible cells by cell color

    I don't see where you define lr in your code. And without sample worksheet and rest of your code, it's hard to say what's exactly wrong with it.

    If you upload sample workbook (with sanitized data), it'd help in pinpointing where the issue is.

  5. #5
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Count only visible cells by cell color

    CK76,

    Yes it is defined but yes hopefully this will help. I have uploaded the sanitized version. Hopefully it's readily apparent. Thank you so much for the review.
    Attached Files Attached Files

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

    Re: Count only visible cells by cell color

    Ah, my bad. SpecialCells property can't be used in UDF (when used in Worksheet).

    If you use it in code to return value. It will work.
    Is there a reason why you are using formula instead of putting in values directly from code to range lr + 2?

    EDIT: As for below part. Your formula is calculating for range N7:N17 hence only 1 or 0.
    Please Login or Register  to view this content.
    You will need to edit it like below to make it dynamic.
    Please Login or Register  to view this content.
    Last edited by CK76; 02-07-2017 at 08:32 AM. Reason: See Edit:

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

    Re: Count only visible cells by cell color

    Test below modification to ColorFunction. This one uses cell's row# to check if entire row is hidden.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Count only visible cells by cell color

    CK76,

    That works perfect. I had help with the original code from another very skilled contributor. I am not able to write that. Hopefully I will gain knowledge over time but I am not exposed to writing VB regularly. I appreciate yours and everyone else that has helped me to get this spreadsheet functioning well.

    Thank you so much!

  9. #9
    Registered User
    Join Date
    04-24-2019
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    1

    Re: Count only visible cells by cell color

    I am trying to count only visible cells by color, and am having a hard time following what you did here.

    I have code that I got off the web that will give me a SUM, but not a SUBTOTAL. I an not sure if it is OK to post that code here, so I won't as of now.
    Just wondering if someone here has a simple piece of code to give me a subtotal of cells that have a specific color index in a column.

    Thanks for any help you can supply.

+ 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. User defined function- count color and count only visible rows
    By marsjanik1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2016, 03:05 PM
  2. Count cells based on cell color
    By vamshi57 in forum Excel General
    Replies: 3
    Last Post: 04-14-2014, 02:11 AM
  3. [SOLVED] Only Count Cells with Specified Cell Color without using VBA
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2014, 12:54 AM
  4. [SOLVED] Referencing Visible Cells Only: Filtered Alternating Cell Color
    By m.r.quinton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2012, 04:03 PM
  5. [SOLVED] changing a current mod (count unique visible cells ->count unique visible cells criteria
    By liranbo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2012, 03:58 AM
  6. Replies: 0
    Last Post: 09-27-2006, 02:05 PM
  7. Replies: 1
    Last Post: 06-16-2006, 08:55 PM

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