Macro/command button to reset all filters in pivot table

hi there,

i have a pivot table with over 4000 entries and about 12 columns which i filter to get the info i want.

Unfortunately with so many rows and columns in the table, if i do a macro that gets each filter back to 'show all' its really slow.

Doing this manually using the "Show all" option in the filter takes a fraction of a second, so surely there must be a way to do this more quickly in VBA?

i can't understand why manually it takes no time at all, but automating it through recording a macro takes forever.

Many many apologies. I misread your post - I thought you were using the auto filter. My original question still stands - what does your code look like?

One thing you may want to try is to surround your code like so...

Please Login or Register  to view this content.

hi,

thanks for the response.
Below is what the macro looks like.

Please Login or Register  to view this content.

As you can see its massive, i actually had to delete 40% of it to fit it in this post, so you can see why it takes so long, because i guess its individually 'ticking the box' of every single field in the 8 odd columns i want it to 'show all' for in the pivot table.

When I run it, if i left it to complete the macro, it would probably take hours, i just have to stop the action by pressing escape. Surely theres a better solution to get it to 'show all', as i say, if when done manually it only takes a few seconds per column? I don't understand why it isn't recording the process behind when you do it manually, instead of doing what it's doing (the code above).

Try wrapping the code in the

Please Login or Register  to view this content.

I ran again a very small pivot table using the recorded macro and it took about two seconds to run the macro and I was restoring only 8 items. With the Application.ScreenUpdatings it was instantaneous results.

I forgot to ask, what version of Excel are you using? I've been testing on 2000 which doesn't have a Show All options for the filters.

http://www.contextures.com/xlPivot03.html
vba should still work on later excel versions

i tried 'wrapping' the code as you suggested, didn't seem to work for me, clearly doing something wrong.

How do i wrap the code that i posted on here with that application screen updating thing i.e. where abouts does it go exactly?

after sub() and before end sub

i had the same issue.. it selects each entry as a separate check.. iot wont do a show all.. have u solved this problem yet?