Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”.
Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.
Remove the Calculated Fields
I ran into this old familiar problem on the weekend, while recording a demo video for my Pivot Power Premium add-in. One of the new features lets you select cells in multiple value fields, then click a button to remove them. It worked perfectly before, but wouldn’t remove the values in this pivot table.
Ah, those were calculated fields, and change their orientation to xlHidden doesn’t work. I had to change the code, so it would work on those fields too. And that’s not as easy as you’d hope.
Delete and Re-Add Calculated Fields
In the past, I used a macro that deleted each Calculated Field, then re-added it to the pivot table. That was okay if only one pivot table used the pivot cache. But, if you had multiple tables on that cache, it didn’t put the calculated fields back in those other pivot tables. Oops!
The same code was on my pivot table blog, and in one of the comments there, Rory Archibald mentioned using the Visible property for an item in the Values field. He posted a sample that hid a specific field, and I finally adapted that code, so it hides all the calculated fields. Thanks Rory!
Except the last one, of course, if you don’t have any non-calculated fields in the Values area. If that Values field disappears, the trick doesn’t work.
Video: Macro to Remove Pivot Table Calculated Fields
In this video, you can see the error that occurs when you try to remove a calculated field with a recorded macro. Then, see the code that actually works!
Download the Sample File and Code
To follow along with the video, you can download the sample file with the macro to remove pivot table calculated fields. It’s on the Pivot Table Calculated Fields page on my Contextures site.
The file is in xlsm format, and you’ll have to enable macros when you open the file.
____________________