PDA

View Full Version : [SOLVED] unselecting all PivotTable rows in a PivotField?



DaveK
09-22-2008, 02:26 PM
Hi,

I need help on how to 'deselect" a whole bunch of PivotFields in a PivotTable. Recall, in the normal excel user interface, a user can click on a row field and a listbox pops up, allowing you to selectively choose which row fields to see or hide. You can then click on SHOW ALL at the top of the list, and it toggles to have EVERY field OFF... then you can individually select the desired fields.

My situation is that I have thousands of unique rows (PivotTable fields) in the rows... and I need to turn them all OFF (hide them in the pivot table), and then enable just one specific row field. This is easily accomplished in the user interface.. but I cant figure out how to do this in VBA.

IN VBA, it seems you have to INDIVIDUALLY 'hide' each and every field you DONT WANT in the Pivot Table.

See code:



ActiveSheet.PivotTables("SAS PivotTable_1").AddFields RowFields:=Array( _
"Group", "in GEMS?"), ColumnFields:="CED Start Date", PageFields:="Status"
With ActiveSheet.PivotTables("SAS PivotTable_1").PivotFields("in GEMS?")
.Orientation = xlRowField
.Position = 2
End With
Range("D6").Select
ActiveSheet.PivotTables("SAS PivotTable_1").PivotCache.Refresh
With ActiveSheet.PivotTables("SAS PivotTable_1").PivotFields("in GEMS?")
.PivotItems("(blank)").Visible = False
.PivotItems("280445131").Visible = False
.PivotItems("280449531").Visible = False
.PivotItems("280496931").Visible = False
.PivotItems("280462388").Visible = False
.PivotItems("280447734").Visible = False
.PivotItems("280370143").Visible = False
.PivotItems("280451535").Visible = False
.PivotItems("280451534").Visible = False
.PivotItems("280451533").Visible = False
.PivotItems("280482752").Visible = False
.PivotItems("280503532").Visible = False
.PivotItems("280503531").Visible = False
.PivotItems("280412331").Visible = False
.PivotItems("280403544").Visible = False



etc etc etc... thousands of fields I need to all HIDE... yet VBA seems to want to hide them all individually. And I dont even KNOW what all the items will be after the refresh... so ....

What I need to do is what you can do in the user interface... toggle them ALL to OFF and then set one specific item as visible:



.PivotItems("#N/A").Visible = True



How can this be done?

THANKS for your help.
Dave

Bob Phillips
09-22-2008, 03:57 PM
With ActiveSheet.PivotTables("SAS PivotTable_1").PivotFields("in GEMS?")
For Each itm In .PivotItems
itm.Visible = itm.Name = "#N/A"
Next itm
End With