VBA, Pivottables, and the (Multiple Items) Challenge

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
I have a PageField on one of my pivot tables that needs to include both 9999 and 99999 using Multiple Items (drag it down to a column field, hide all elements but 9999 and 99999, then drag up to the page field and select (All) will yield (Multiple Items) instead and will have just 9999 and 99999 selected). My problem is coding this. When a new element is added to the Pivot table, say 55, by default it is visible, so now my pivot table will include 9999 and 99999. My solution to this is:

Code:
Sub CheckMultItems()
Dim PVTITM As PivotItem
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
    If PVTITM.Name = "9999" Or PVTITM.Name = "99999" Then
            PVTITM.Visible = True
    Else
            PVTITM.Visible = False
    End If
Next PVTITM
End Sub

The problem is that this is SLOW, for each of the 1000 items in the pivotfield, it recalculates the pivottable (even if calculation is set to manual), even if its Visible status did not change. There should be a much faster solution since really, I only want to hide 55 (or whatever other new fields may have slipped in). So I wrote:

Code:
Sub CheckMultItemsFAST()
Dim PVTITM as PivotItem
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
    If PVTITM.Name = "9999" Or PVTITM.Name = "99999" Then
        If PVTITM.Visible = False Then
            PVTITM.Visible = True
        End If
    Else
        If PVTITM.Visible = True Then
            PVTITM.Visible = False
        End If
    End If
Next PVTITM
End Sub

The problem here is that PVTITM.Visible is ALWAYS FALSE. This apparently is true for any Page Field. I could write
Code:
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
            PVTITM.Visible = True
            Debug.Print PVTITM.Visible
Next PVTITM
and will get nothing but FALSE. Why is it always false? and how can I tell if it is really false? (meaning that it doesn't show up in the (Multiple Items) list)

Thanks,
~Gold Fish
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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