Limiting/Hiding PivotItems

[jomili]

In my macro I use a basic routine twice to only show a small selection from a vast array of pivot items (Pi and PT are previously defined):

Please Login or Register  to view this content.

The oveall macro works just fine, but Extremely Slowly. I have Manual Updating turned to True, and Screen Updating turned off. Walking through the code, it's these two PivotItems sections that are slowing everything down. Is there a faster way to do this?

Any help is highly appreciated.

[JieJenn]

Can you post a sample wb?

[jomili]

Not really. The pivot I'm manipulating is in a workbook from our IT group. It's based on a huge datasource, to which I have no acess other than through the pivot. I can doubleclick and pull up some of the data, but it's a truly large set, and includes Names and Pay information. If I were to limit the data I'm sure things would go easier and faster. But I CAN show you how many fields are in the two PivotItem categories; see attached.

[JosephP]

using pivotfilters should be a lot faster than looping through all the pivotitems

[JieJenn]

Maybe something like this

Please Login or Register  to view this content.

[jomili]

Jiejenn,
I tried it your way, no difference in time.

JosephP,
Okay, I'll bite; how do we use "pivotfilters" in this case, via VBA?

[JosephP]

my bad-not enough coffee. the approach I was thinking of is for autofilters and I don't suppose your data source is olap?
how many items are you looping through? I'm on a cell so can't see your workbook.
you may be able to speed it up by testing if each item is visible and then only hide it if it is and it's not a department you want.

[jomili]

For departments, there are only 46, of which I want 6.
Of Posn Func, there are 745, of which I want 4.

[JosephP]

can you alter the query that pulls from the data source? what is the data source-a db or cube?

[jomili]

Nope, the workbook, including query and datasource, is locked down tight. Literally all I have access to is the pivot table itself. I COULD double-click the Grand Total, and from the page that's generated create a new pivot table, but seems like that would take even longer.

[JosephP]

ok. have you tried
-turning off subtotals
-setting the field sort to manual
-testing visibility before setting it
-turning off calculation and events

[jomili]

I guess I should have posted the whole macro to begin with. I've also attached a picture of how the PT looks when I come into it. There are 92 "Par_Program" lines. Each Department has about 70 of those. Each Par_Program has up to 745 Posn-Funcs. So you can see there's a mass of data available. My job in this macro is to whittle it down to two specific views.

Please Login or Register  to view this content.

[JosephP]

this is about all I can think of to try if you can't alter the data source

Please Login or Register  to view this content.

[jomili]

JosephP,

Thanks so much. I like the way you rearranged it all; that shows me a few tricks I hadn't thought of. I hope you don't mind if I ask some questions:

1) What's the purpose of these two lines of code, how do they help?

Please Login or Register  to view this content.

2) I see where you turn Calculation to Manual, but don't see where you turn it back to auto. And what is the meaning of these two lines?

Please Login or Register  to view this content.

Thanks,
John

[JosephP]

1. turning off sorting on a pivot field is an old trick used when hiding/showing items. I'm not sure if it makes any difference in newer versions of excel but it used to be invaluable in preventing errors.
2. the first line stores the current calculation setting (since it might not be automatic) and then that setting is restored by the second line. it's better practise than simply resetting to automatic calculation

[jomili]

Followup-The selection for the Posn Func codes isn't happening. I'm walking through the code to find out why.
Update-had to change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

---------- Post added at 07:41 AM ---------- Previous post was at 07:33 AM ----------

JosephP,

I crossed over your last post, so just read it. Thanks for explaining, especially the 1Calc piece. I've always just returned calculation to automatic; nice to know there's a way to restore it to whatever it was when I came in.

Thanks for all your help on this. I don't know that we achieved much of a speed difference, but the code is a lot tighter and more efficient now, so I'm pretty happy. Thanks for helping and teaching.

[JosephP]

my pleasure. I still have this idea in my head that pivotfilters ought to accept an array of values-as the pivot field dropdown effectively does-without having to loop (in olap pivots you can create a visibleitemslist). maybe in office 16 ;-)

[jomili]

I'm not going to hold my breath, and I suggest you don't either.