Hide/unhide cells when expanding/collapsing pivot table fields

Hi everyone,

So I spent a couple of days searching for an answer, but I've had no luck so far. I am working on a dashboard and have a few pivot tables one right on top of the other. I have enough rows hidden between them so that I don't get the pivot tables cannot overlap another error, but the code I'm using seems inefficient. I want the rows to unhide when they become populated with data, ie after I expand a field, and hide the rows when they are empty.

Here is the code I am currently using.

Please Login or Register  to view this content.

The problem is that every time I expand or collapse a field the for loop checks through all of the cells and can take a couple seconds, which does not seem user friendly for the people who read the report. I am trying to figure out how to exit out of the for loop when it unhides the necessary amount of rows, ie. if I expand a field and it populates 10 cells with data, then I want the macro to unhide the 10 cells and then stop. When I collapse a field I want the macro to hide the cells that no longer have any data in them and then stop the for loop there without looping through all 84 rows.

I hope this is enough information. Please let me know if other details are required.

Thank you!

bump 10char

bump 10char

You can put this code in your worksheet

Please Login or Register  to view this content.

Awesome, this works perfectly. Thank you jimrosser!

Ah, one question. How would I go about implementing this to work on multiple pivot tables on my worksheet. Say I have 2 pivot tables on the top row and two on the bottom.

Not sure I'm following you.
But you could paste the code below and change the parameters.

Charliee,

You may want to change your event code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

This should will make your sheet work faster.