Trying to create a macro that uses a formula on only visible cells.

I have a macro that I'm working on, and for the most part the macro works well except for one issue. When the source data is exported, it will be over the course of several months, and the end user will filter out all of the months except the one they want to look at. I need to have the macro calculate only for the visible, non-filtered items. Here is the code for my macro (many of the lines are commented out since I had used them at some point in testing, but currently don't need them:

Please Login or Register  to view this content.

The main issue is that when determining the percentage of a response, the denomenator is calculated based on ALL values, not just the visible ones. For example, if the spreadsheet has 9 entries, 1 row for labels, 7 rows for May, and 1 row for april, and I filter out april, when the % of yes responses for may is calculated by the macro its going to be x/8, when really it should be x/7 since the row containing the month of April is filtered out.

Hey there,
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



The solution is quite simple, after you add code tags.

Great thanks. The solution you need is actually a formula, rather than VBA, the formula for this would be:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2,ROW(F2:F10)-ROW(F2),0)),--(F2:F10="Yes"))/SUBTOTAL(3,F2:F10)

(Where I picked 10 as TheAnswer row)

Putting this in your code:

Please Login or Register  to view this content.

(Not tested - no workbook)

HTH