Macro to Remove Pivot Table Calculated Fields

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.

Macro to Remove Pivot Table Calculated Fields error

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.

____________________

You may also like...

4 Responses

  1. Doug Glancy says:

    This post could be titled “Another Reason to Avoid Calculated Fields.” ;-)

  2. mrbougles says:

    Hi – this is great. This website is great.

    I’m looking to build:
    one macro to hide a specific field in a specific pivot table and another macro to show it again.

    Struggling to get it working. I can imagine others trying the same thing also.

    Are you able to help with some advise?

    Thanks.

  3. mrbougles says:

    Hi – please feel free to disregard my last comment. I got your macro @
    http://blog.contextures.com/archives/2010/09/08/remove-pivot-table-calculated-field-with-excel-vba/

    to do the trick. I was not thinking straight. its a great macro.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *