Yesterday, I started out with the best of intentions, planning to get some work done, and find a couple of topics for upcoming blog posts. Then, while sipping my morning coffee and reading the RSS feeds, I clicked on an article about pivot tables. There were a few lines of sample code for creating a list of fields in the pivot table, which seemed like a good idea, but needed improvement. And there went the day!
But, in the end, I had some useful code for removing calculated fields from the pivot table layout, so the day was productive, but not in the way that I anticipated.
Strange Results for Orientation Property
The journey started out with that simple list of pivot table field names. I wanted to see more information about each field, so I wrote some Excel VBA code to list the pivot field caption, source name, location (orientation), position, sample data, and a formula (calculated fields).
Everything worked well, but the Orientation property gave some strange results for the fields in the Values area. Instead of listing them as Data, they were shown as Hidden.
Check the Data Fields
Next, I tried a different approach, looping through each type of pivot field separately, e.g. RowFields, DataFields. In that list, the orientation was correct, but no sample items were shown for the data fields, and the calculated field formulas weren’t listed.
Removing the Data Fields
Going back to the first code example, I played with the Orientation property in the code, but had no success in getting it to display correctly when looping through the PivotFields. When I tried to change the Orientation for the data fields, things got even stranger. I could change the regular data fields to xlHidden, to remove them from the pivot table layout, but those calculated fields wouldn’t budge.
Instead of changing the calculated field Orientation, Excel displayed the error message “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”
You can manually uncheck the calculated field boxes, and remove them from the pivot table, then check the box again, to put it back into the layout. However, if you record code while removing the calculated field, that recorded code shows the same error message when you try to run it.
Remove the Calculated Fields
A Google search showed that many other people had encountered this problem, and the only solution seemed to be to delete the calculated field, instead of trying to remove it from the layout. The Google search turned up a line of code to delete the offending calculated field, but that wouldn’t be much help if you wanted to keep the calculated field in your pivot table, for use later.
So, I wrote the following code that deletes each calculated field, then immediately adds it back to the pivot table field list, but not into the pivot table layout. If you’ve been having the same trouble with calculated fields, I hope this helps!
NOTE: See the updated code in the next section, to hide the calculated fields.
Sub RemoveCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim pfNew As PivotField Dim strSource As String Dim strFormula As String Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.CalculatedFields strSource = pf.SourceName strFormula = pf.Formula pf.Delete Set pfNew = pt.CalculatedFields.Add(strSource, strFormula) Next pf End Sub
Hide Calculated Fields
[Update 2016-05-10] The code above removes the calculated field from the pivot cache, then adds it again. That works okay if only one pivot table is based on the pivot cache. However, it’s a problem if there are multiple pivot tables based on that cache. The calculated field is removed from all of them, and only added back to the layout of the active pivot table.
There is a video that explains how this code works on this blog post – Macro to remove calculated fields
Thanks to Rory Archibald, who suggested the changing the Visible property instead.
With ActiveSheet.PivotTables(1).DataFields(“Sum of NewTax”) .Parent.PivotItems(.Name).Visible = False End With
Here is the code, that will hide all the Calculated Fields from the pivot table for the active cell:
Sub RemoveCalculatedFields() 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