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.

You'd use Range.SpecialCells(xlCellTypeVisible).

So something like...

Please Login or Register  to view this content.

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!!

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.

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.

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.

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!

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.