VBA Code for Removing All Value Fields in a Pivot Table

**lufcluke** · 11-05-2009, 11:04 AM

Hi all,

New to the forum and had a quick look through the search and couldnt find this query already here.

Basically I'm looking for VBA code to remove all of the Value Fields present in a PivotTable. I dont want it to be Field specific such as the below code that I'm currently using:

Please Login or Register  to view this content.

More like something similar to this: (But for Fields instead of Filters!)

Please Login or Register  to view this content.

I have attached an example to show the starting point and the desired result.

Any help would be appreciated.

Thanks!

Please see below:

**DonkeyOte** · 11-05-2009, 11:23 AM

By value fields I presume you mean Data Fields ... at which point something along the lines of the below perhaps ?

Please Login or Register  to view this content.

**lufcluke** · 11-05-2009, 11:51 AM

Thanks!

That works apart from on Calculated Fields I think?!

Can I manipulate the code to remove these as well?

**lufcluke** · 11-05-2009, 12:08 PM

Thanks!

That works apart from on Calculated Fields I think?!

Can I manipulate the code to remove these as well?

**DonkeyOte** · 11-05-2009, 12:30 PM

Yes, though you need to adapt the code slightly...

Please Login or Register  to view this content.

**m1806** · 11-07-2009, 02:20 AM

please send me who to import data from yahoo finance stock name last trade price day high day low

**DonkeyOte** · 11-07-2009, 03:56 AM

m1806,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread.

**hitxrafa** · 11-22-2012, 11:09 AM

Dim PT As PivotTable, ptField As PivotField
Set PT = ActiveSheet.PivotTables("PivotTable2")
For Each ptField In PT.DataFields
ptField.Orientation = xlHidden
Next ptField
Set PT = Nothing

Donkey, regarding the same doubt of the colleague, is it also possible to add a condition, like if I want to keep one or two data fields and remove the others?

Thanks in advance

**Offek** · 01-23-2015, 01:09 PM

sorry for being late for a dinner, but maybe someone find it useful. My approach is to iterate through all column names which I use for a pivot and by suspending errors it hides all of the fields I use as a row in pivot:

On Error Resume Next
For i = 1 To ActiveSheet.PivotTables("YTD_RevRec").PivotFields.Count
ActiveSheet.PivotTables("YTD_RevRec").PivotFields(i).Orientation = xlHidden
Next
On Error GoTo 0

so if you want to keep any for the rows just add there an if statement, something like this:
if .PivotFields.name = "whatever" then...