Advance Pivot Table issue

[wadere]

Help!
Appologies if this has already been answered before (looked but I could not find). I need to speed up some code that works on a VERY large pivot table (real db not an option).

I need to insure all pivot items in a pivot field are not hidden. I know I can use the hiddenitems property to show each hidden item in VB, but this is very slow (especially if there are a lot of hidden items in the field).

Is there a way (VB only) that will "SHOW ALL" items (changing the pivotitem.visible = false to true) all at one time?

Thanks greatly

 

[pgc01]

Hi wadere
Welcome to the board

Not exactly what you want but you can loop through the fields and for each one set the ShowAllItems property to True. This should be much quicker than going item by item.


Hope this helps.
PGC

 

[wadere]

Thanks....actually that is what I was attempting to do, but I always get a macro error when I attempt to do so... this is a quick example of the code.

set Piv1 = Worksheets("NAME").Pivottables("P1").Pivotfields("stuff")
piv1.showallitems = true <------- this always gives me an error.

For the time being I have only worked on the "hidden items" This at least keeps the code from looking at the visible items.....

For each xItem in Piv1.Pivotfields("stuff").hiddenitems
xItem.visible = true
next xItem

Any thoughts?

 

[Dufus]

wadere,

Don't forget the basics for speeding up your code...

With Application 
        xlCalc = .Calculation 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
"YOUR CODE HERE"
With Application 
    .Calculation = xlCalc 
    .ScreenUpdating = True 
    .EnableEvents = True 
End With

Dufus

 

[wadere]

Dufus:

I agree....that is always good advice. I have also removed all select statements, done all mass calculations (one time type only) via vba arrays instead of in the cells..... have been able to cut my runtime from 3 minutes down to just 30 seconds...using timing routines, about the only thing I can improve from there looks to be "un-hidding" the hidden data in my pivot tables.

 

[headtoadie]

I'm looking for the same answer. I was hoping to use arrays to write the visible TRUE/FALSE state of all PivotItems at one time.

Both VisibleItems and HiddenItems can be called en masse using an array. I was hoping I could do the same with PivotItems, but I cannot get it to work.

--------------------------------

In this example, aryPvtItemIndex is an array of of index #s from 1 to n. aryShowHide is an array that contains the TRUE/FALSE state for each index number. (The examples below exclude the pivottable identification and start at the pivotitems.)

If I run this using the index number it does not crash, but it also doesn't seem to change the state to match aryShowHide.

.PivotItems(Array(aryPvtItemIndex)).Visible = Array(aryShowHide)


If run using an array of pivotitem names it does crash.

.PivotItems(Array(aryPvtItemName)).Visible = Array(aryShowHide)


----------------------------

Does anyone know if it is possible to use arrays to update all pivotitems at one time?

-HT