Pivot Table - How do I "Show All Data" of a particular field?

[MikeZz]

Hi Experts,
I have a pivot table with some fields containing a large amount of items.
I want to have a button that first makes ALL items visible in a particular
field and then just hides certain ones. This way as more values are added,
the macro doesn't have to worry about clearing items unknown to me now.

The Record Macro works great to specify exactly what I want to make visible
or not but it I was wondering if there's a global property for a field to
make all items visible? Or do I have to go through each item, see if it's
visible and if it's not, set visible = true?

Also, I'm not sure if there's a way, but maybe if someone can show me other
ways to speed up pivot changes such as turning off refreshing & calculation
etc and then turning it back on may be of help.

Thanks for the help,
Mike Zz

Below is the subroutine I have from recording a macro.
All is original except where I tried to use If items.visible = false then
make it true.
That made a little improvement but it still seems to be a little slow on the
refresh.


Sub Set_Pivot()
'

ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier").CurrentPage = _
"XXX"
' Range("A5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM Plant")
.PivotItems("Plant 1").Visible = False
End With
' Range("A7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
If .PivotItems("ISU").Visible = True Then .PivotItems("ISU").Visible
= False
If .PivotItems("SUZ").Visible = True Then .PivotItems("SUZ").Visible
= False
If .PivotItems("WUL").Visible = True Then .PivotItems("WUL").Visible
= False
End With
' Range("A8").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Model")
If .PivotItems("SGM12").Visible = True Then
..PivotItems("SGM12").Visible = False
If .PivotItems("SGM18").Visible = True Then
..PivotItems("SGM18").Visible = False
If .PivotItems("SGM200").Visible = True Then
..PivotItems("SGM200").Visible = False
If .PivotItems("SGM201").Visible = True Then
..PivotItems("SGM201").Visible = False
If .PivotItems("SGM258").Visible = True Then
..PivotItems("SGM258").Visible = False
If .PivotItems("SGM308").Visible = True Then
..PivotItems("SGM308").Visible = False
If .PivotItems("SGM618/J200").Visible = True Then
..PivotItems("SGM618/J200").Visible = False
If .PivotItems("SGM985").Visible = True Then
..PivotItems("SGM985").Visible = False
If .PivotItems("SGME10").Visible = True Then
..PivotItems("SGME10").Visible = False
If .PivotItems("SGME11").Visible = True Then
..PivotItems("SGME11").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM").CurrentPage =
"GM"
' Range("B12").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PAC")
If .PivotItems("EL").Visible = True Then .PivotItems("EL").Visible =
False
End With
End Sub

 

[Barb Reinhardt]

Mike, this should get you started. This particular piece of code will set
all pivot items in all pivot tables in the workbook so that they are visible,
but you should be able to get the idea.

Sub test()
Dim myPivotItem As PivotItem
Dim myPivotTable As PivotTable
Dim myPivotField As PivotField
Dim myWS As Worksheet

For Each myWS In ThisWorkbook.Worksheets
For Each myPivotTable In myWS.PivotTables
For Each myPivotField In myPivotTable.PivotFields
For Each myPivotItem In myPivotField.PivotItems
Debug.Print myWS.Name, myPivotTable.Name, myPivotField.Name,
myPivotItem.Name, "Visible: " & myPivotItem.Visible
If Not myPivotItem.Visible Then
myPivotItem.Visible = True
End If
Next myPivotItem
Next myPivotField
Next myPivotTable
Next myWS

End Sub

 

[MikeZz]

Thanks Barb,
So I take it there is no single quick command to make all visible?
Is there a way to turn off the table re-building after each item change and
just rebuild it once at the end?

The reason I ask is because in Auto Filters, there is a one-liner command
that will clear all autofilters on a page at once (while leaving the
drop-downs) and this was magnitutes quicker than checking each item in the
autofilter. I was hoping for the same here.

In my case, I could have over 100 items in a pivot field and it would take a
relatively long time to go through each item because it seems like the pivot
table has to rebuild itself after each item I change.

I've noticed on my pivot table (which draws from about 30k records), if I
use the Pivot Table Wizard to completely change the Pivot Layout, it takes a
lot less time to rebuild the table than if I make each indvidual change and
let the table rebuild after each change. When you use the Wizard, Excel only
rebuilds the table once after all the changes are defined. If you use the
drop-down field boxes, excel has to rebuild the table after every change that
is made....

I guess I can't explain it any other way but there's got to be a more
efficient way.

Thanks again,

 

[Barb Reinhardt]

Have you tried putting this at the beginning of your code
Application.ScreenUpdating = FALSE
And this at the end
Application.ScreenUpdating = TRUE

 

[MikeZz]

Hi Barb,
I tried this and just one field has 75 items.
It takes about 7 seconds to go through all 75 items and set them = visible.
It takes about the same if I turn off screen updating.

I also tried the following test:
Dragged that field into the pivot table (was above table as selectable option)
Used the drop down to uncheck "Show All" then just pick ONE of the 75 items.
Then I used the same drop down and checked "Show All"

The pivot table updates in a blink of an eye as opposed to 7 seconds.

This is what I'm talking about... there has to be some way Excel does the
update a lot faster than going through each item and setting it to visible.

Thanks for trying though.

 

[Barb Reinhardt]

How about adding

Application.Calculation = xlCalculationManual at the beginning and
Application.Calculation = xlCalculationAutomatic at the end.

I did record a macro to show all and it changed visible for each and every
item.

 

[MikeZz]

Hi Barb,
I tried using with and without hiding and showing the exact same value.
I also created a timer in the code that tells me how long it takes to
execute now.
It takes 7-8 seconds if I turn off auto calc and 15 seconds if I leave it on.

It's a big improvement but I can still do it by hand quicker than that.
There's got to be a trick to it...