If you record a macro while you remove pivot table calculated fields, then try to run that macro later, you’ll get an error message. To solve that problem, use my macro to remove pivot table calculated fields. The code is shown below, and there’s also a sample file with the macro. To see all the steps, watch the short video.
Recorded Macro Error
When you try to run your recorded macro to remove pivot table calculated fields, an error message will appear:
Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class
This video shows the error that appears when you run the recorded code. It also shows how to use my macro, to fix the problem, so the calculated fields are deleted without any error messages. There are written instructions below the video.
Help Button
When you run your recorded macro, and the error appears, there’s a Help button, but it doesn’t really help. When I clicked the Help button, it took me to a Microsoft site page with general information about working with Excel VBA.
It would take more searching there, to find any useful information – if you’re lucky enough to find it!
End or Debug
There are 2 other buttons in that error message – End and Debug.
- If you click End, the macro stops running, and nothing else happens
- If you click Debug, the code window opens, with a line of code highlighted.
Here’s my recorded code, with yellow highlighting on the line that tries to hide the Sum of TaxNew calculated field.
NOTE: I added line breaks, to make the lines shorter for the screen shot
In the title bar of the Visual Basic Editor (VBE) window, you can see the file name, and the module name. The code has temporarily stopped, and the title bar says [break]
Fix the Macro
While you’re looking at the code, if you see an obvious problem, you could try to fix it. Then, press the F5 key on your keyboard, to continue running the code.
In this case, I don’t see any obvious problem, so I won’t try to fix the code.
Stop the Macro
Since the macro isn’t running correctly, and I don’t see a quick way to fix the macro, I’ll stop it.
- To stop the macro, click the Reset button on the VBA toolbar.
The highlighting will disappear, and the macro stops running. The “[break]” disappears from the VBE title bar.
Replace the Code
Much of the time in Excel, if you record a macro, you can play it back, and it works nicely. Sometimes you need to make minor adjustments to the code, so that it works in different locations, or other small tweaks.
In this case though, there aren’t any adjustments that we can make, to fix the code. The recorded code would work for a normal pivot table field, but it doesn’t work for calculated fields.
I’ve tried all kinds of tweaks and changes, and nothing worked! We have to start from scratch, in this case.
Macro to Remove Pivot Table Calculated Fields
Instead of trying to write your own macro to remove pivot table calculated fields, copy the code that’s shown below. Then, paste it into your Excel workbook, in a regular code module.
This code can also be found on my Contextures site, and there is a sample file there too, with the macro and a pivot table that you can use for testing.
Notes About the Macro
The following code removes all the calculated fields by changing the Visible property to False. Thanks to Rory Archibald, from Excel Matters, for suggesting this solution.
There are a few things to note about this macro:
- The macro will only work if there are 2 or more fields in the pivot table’s Values area
- If other pivot tables are connected to the same pivot cache, the calculated fields in those pivot tables will NOT be removed
- The last field in the Values area will not be removed. So, if there aren’t any normal fields in the Values area, the last calculated field will not be removed
The Macro Code
Copy this code to a regular code module in your workbook. Then, select a pivot table cell, and run the macro. Only the selected pivot table is affected.
Sub RemoveALLCalculatedFields()
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then
MsgBox "Select a pivot table cell"
Exit Sub
End If
For Each pf In pt.CalculatedFields
For Each df In pt.DataFields
If df.SourceName = pf.Name Then
With df
.Parent.PivotItems(.Name) _
.Visible = False
End With
Exit For
End If
Next df
Next pf
End Sub
Video: Create Pivot Table Calculated Field
In a pivot table, you can create your own calculated fields. In this video’s example, I show how to calculate each salesperson’s bonus, based on their total sales.
Get the Sample File
To follow along with the video, 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, so be sure to enable macros when you open the file, if you want to test the macro.
Macro to Remove Pivot Table Calculated Fields
__________________________
____________________________