Hide / Unhide Pivot table Data Items

[hoppermr2004]

I have multiple data items in my pivot table. If I click on the grey 'Data' tab drop down, I can hide data fields that I don't wish to view by unticking them (this shouldn't be confused with hiding row or column data fields although the effect is the same).

The equivalent VBA command would be :-

Sheet1.PivotFields("Sum of No Records").Orientation = xlHidden

If I wish to unhide a data field hiden in this way, that item is no longer in the data field drop down box. Effectively the pivot table has removed the data field from the pivot rather than hidden it.

Is there any way to effectivly 'hide' and then 'unhide' data items without having to drag the data field back into the pivot?

Thanks dudes

 

[Andrew Poulsom]

No, to reverse the hiding:

ActiveSheet.PivotTables(1).PivotFields("No Records").Orientation = xlDataField

 

[hoppermr2004]

Thanks MVP - that worked fine.

Might be getting greedy here but can you specify the order of the data field?
Something like?

ActiveSheet.PivotTables(1).PivotFields("No Records").Orientation.Order = 1

Thanks again

 

[Andrew Poulsom]

Close:

ActiveSheet.PivotTables(1).PivotFields("Data").PivotItems("Sum of No Records").Position = 1

 

[hoppermr2004]

legend

 

[Jhc]

Hopefully someone will get an update on this thread. I'm a programmer, but relatively new to Excel vba. I tried entering the following:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotFields("FNAME").Orientation = xlDataField
...
End Sub

To rebuild the pivot table whenever it is activated, however, if the field is already visible, Excel creates a second field named "FNAME2", "FNAME3", ... How can I only set this if the field doesn't exist? I'm sure this is a basic question, but I haven't come up with an answer after many searches.

Thanks

 

[Andrew Poulsom]

I can't reproduce that behaviour in Excel 2003. Can you post a small sample of your data?

 

[arjunat]

Hopefully someone will get an update on this thread. I'm a programmer, but relatively new to Excel vba. I tried entering the following:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotFields("FNAME").Orientation = xlDataField
...
End Sub

To rebuild the pivot table whenever it is activated, however, if the field is already visible, Excel creates a second field named "FNAME2", "FNAME3", ... How can I only set this if the field doesn't exist? I'm sure this is a basic question, but I haven't come up with an answer after many searches.

Thanks

did you manage to resolve this? if so how?

 

[arjunat]

I can't reproduce that behaviour in Excel 2003. Can you post a small sample of your data?

Hi Mr Poulsom, wonder if you've got a possible solution to this ? i'm using Office365, encountering the same issue.
I'd like to ideally hide all the columns before rerunning a macro to show one specific column.