Count non-hidden cells in a pivot table

[mae0429]

Hello ever-helpful VBA gurus,

I would like to know of a way I can check if a cell is hidden or not, and if it is not hidden, count it in a summation. Only problem is, I want this to be a real-time cell on the sheet, as it will reflect the number of items in a pivot table. That way, the user can look at the top of the sheet and know how many items fall under that specific drill-down of the pivot (counting by eye is out, as there are over 1000 entries and pivoting will take out random entries).

If any part of that is unclear, let me know.

Thanks and good luck!
-Matt

[mae0429]

I abandoned this about a month ago, but now have come back to it. Is there a way to show, in a cell outside the pivot table, how many items are visible at a given point in time?

Say I have a 100 entries total in a pivot table starting at cell B2, but I pivot them to get rid of everything after the year 10000 BC (for example) and I'm left with 30 entries. I want to be able to have cell A1 show me the number 30. Then, if I change the pivot and 65 entries show, cell A1 will automatically update to show 65 instead of 30. Can this be done?

Thanks,
-Matt

[Aussiebear]

Matt, can you throw up a workbook with some dummy data on sheet1, then your pivot table on sheet 2 please?

[georgiboy]

You could adapt this to your needs, it counts the data in the pivot in sheet2 and updates the count in sheet1 when you change selection in sheet1. The pivot data is in sheet3.

Hope this helps

[tallawah]

I'm looking to do something similar, will try the attached file and see if it works for my needs.