Deselect One Item in Pivot Table

[JohnK]

Hello,

Is it possible to select all page fields and then deselect one, rather than just selecting each page field that you want.

Here is the code:

[VBA]Private Sub selectXFL()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("[State]")
.CubeField.EnableMultiplePageItems = True
.AddPageItem "[State].[All State].[AK]", True
.AddPageItem "[State].[All State].[AL]"
.AddPageItem "[State].[All State].[AR]"
.AddPageItem "[State].[All State].[AZ]"
.AddPageItem "[State].[All State].[CA]"
.AddPageItem "[State].[All State].[CO]"
.AddPageItem "[State].[All State].[CT]"
.AddPageItem "[State].[All State].[DC]"
.AddPageItem "[State].[All State].[DE]"
.AddPageItem "[State].[All State].[GA]"
.AddPageItem "[State].[All State].[HI]"
.AddPageItem "[State].[All State].[IA]"
.AddPageItem "[State].[All State].[ID]"
.AddPageItem "[State].[All State].[IL]"
.AddPageItem "[State].[All State].[IN]"
.AddPageItem "[State].[All State].[KS]"
.AddPageItem "[State].[All State].[KY]"
.AddPageItem "[State].[All State].[LA]"
.AddPageItem "[State].[All State].[MA]"
.AddPageItem "[State].[All State].[MD]"
.AddPageItem "[State].[All State].[ME]"
.AddPageItem "[State].[All State].[MI]"
.AddPageItem "[State].[All State].[MN]"
.AddPageItem "[State].[All State].[MO]"
.AddPageItem "[State].[All State].[MS]"
.AddPageItem "[State].[All State].[MT]"
.AddPageItem "[State].[All State].[NC]"
.AddPageItem "[State].[All State].[ND]"
.AddPageItem "[State].[All State].[NE]"
.AddPageItem "[State].[All State].[NH]"
.AddPageItem "[State].[All State].[NJ]"
.AddPageItem "[State].[All State].[NM]"
.AddPageItem "[State].[All State].[NV]"
.AddPageItem "[State].[All State].[NY]"
.AddPageItem "[State].[All State].[OH]"
.AddPageItem "[State].[All State].[OK]"
.AddPageItem "[State].[All State].[OR]"
.AddPageItem "[State].[All State].[PA]"
.AddPageItem "[State].[All State].[PR]"
.AddPageItem "[State].[All State].[RI]"
.AddPageItem "[State].[All State].[SC]"
.AddPageItem "[State].[All State].[SD]"
.AddPageItem "[State].[All State].[TN]"
.AddPageItem "[State].[All State].[TX]"
.AddPageItem "[State].[All State].[UT]"
.AddPageItem "[State].[All State].[VA]"
.AddPageItem "[State].[All State].[VT]"
.AddPageItem "[State].[All State].[WA]"
.AddPageItem "[State].[All State].[WI]"
.AddPageItem "[State].[All State].[WV]"
.AddPageItem "[State].[All State].[WY]"
End With
End Sub [/VBA]

Thanks,

John

[XLGibbs]

Not sure I follow...if you are building this on the fly....the items in the field list would be automatic based on the data set...however, there is also a difference between page fields and row/column fields.

A page field allows for individual selection form the list to show relevant data, or some items in the list can be hidden (default at time of creation unless otherwise filtered, all items in a page field are visible)

To Select one of the items in a page field..
[VBA]
ActiveSheet.PivotTables("PivotTable1").PivotFields("State").CurrentPage = "MD"
[/VBA]

To deselect a row or column item...
[VBA]
With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
.PivotItems("MD").Visible = False
End With
[/VBA]
You can also cycle through them for code simplicity:

[VBA]
Dim PTI as PivotItem
For each PTI in ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
PTI.Visible = False
Next PTI
[/VBA]

are examples of some ways to manipulate the visibility of certain fields.

Seems like your code is building a cube data set as opposed to just pivot manipulation though... I am not as familiar with the Cube manipulation code wise...

[JohnK]

Seems like your code is building a cube data set as opposed to just pivot manipulation though... I am not as familiar with the Cube manipulation code wise...

Yes, it is. The data is pulling from our SQL server. The problem is that each time a page field is selected, it takes 5-10 seconds for the table to refresh. To go through 48 of the 50 states takes forever, so I'd like to be able to choose all 50 states and then deselect the two that I don't want.

I'm sorry that I wasn't more clear in my first post.

Thanks for your help!

John

[XLGibbs]

Is it always the same two states? It would make more sense to apply a filter to the Get Data query that feeds the pivot table to eliminate the 2 states. This can be set up to be fed by a parameter outside the query (such as a cell location)...

Do you have the ability to edit the query to apply a filter for the two states?

It shouldn't take quite that long to shift a pivot table. Check the table options and check the "save data with table layout" option. The file will grow in size, but you will be able to shift data quicker with respect to manual feild manipulation...

Also, if the field is a page field (not a row or column field) you can just double click the page field header and choose to always HIDE the two states you don't want.

Hope that gives you some more options ...let me know which you need to go...