Advance Pivot Table issue

wadere

New Member
Joined
Sep 21, 2006
Messages
4
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,827
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

New Member
Joined
Sep 21, 2006
Messages
4
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

Board Regular
Joined
Aug 19, 2006
Messages
176
wadere,

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

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

New Member
Joined
Sep 21, 2006
Messages
4
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

Board Regular
Joined
Aug 1, 2003
Messages
219
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top