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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

pgc01

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

Forum statistics

Threads
1,141,227
Messages
5,705,137
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top