Remove Pivot Table Calculated Field With Excel VBA

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.

PivotFieldListCode01

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.

PivotFieldListCode02

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”

PivotFieldListCode03

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

________________

You may also like...

23 Responses

  1. Jeff Weir says:

    Hi Debbra. Got a question related to your blog in general rather than this post specifically, which is: Do you have a comment feed for all comments that I can subscribe to? I enjoy the insights I pick up from the daily dose of excel comment feed (as well as the blog proper) and would like to be able to glean the same insights from your blog if possible.

    Regards

    jeff

  2. Hi Jeff, thanks for asking about the comments feed. I’ve added a link at the bottom of the right sidebar.
    The link is: http://feeds.feedburner.com/ContexturesBlogComments

    Debra

  3. Ashish Jain says:

    Awesome ! Was stuck in it for last 10 minutes and you saved my next 50 minutes :) Thanks again :)

  4. Erwan de Kerg says:

    Hi Debra,

    Topics really interesting. I experimented your code RemoveCalculatedFields() ; its OK in Excel 2007, but NOT in Excel 2003 SP3.

    my problem is that, using Excel 2003, I want to remove all the CalculatedField from the DataField zone in my PivotTable with about 12 CalculatedField and 3 DataField.
    I tried the Orientation property without success (as you explained it in your blog)

    As i want to remove all the 12 CalculatedFields and keep the 3 others, I tried then your code :
    For Each pf In pt.CalculatedFields
    pf.Delete
    Next pf

    but it doesn’t work in my Excel 2003 SP3.

    Any ideas ?

    many thanx

  5. @Erwan, the code works for me in Excel 2003 SP3. What happened when you tried it? What didn’t work?

  6. Ryan says:

    Thank you so much… this worked perfectly. I was about to lose it when the Caculated field would not hide.

  7. Jeff C says:

    Thank you! Thank you! Thank you!

    I’ve been hampered by this for much longer than I care to admit. After spending all morning googling I finally found your solution.

    Great work!

  8. Villa says:

    Excelent work, i spent a lot of time into this, thank you so much.

  9. David says:

    My calculated field (1) depends from another calculated field (2) so when i use this macro, my formula(1) colapse and it delete the reference from my other calculated field (2).

  10. Rob says:

    THANK YOU SO MUCH!!! I have been looking for this type of code for a while now….

  11. StarknightSK says:

    Amazing work here! Works smoothly! Adds a great feature to a large project I’m working on. Thank you debra.

  12. marcelita03 says:

    Genious!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  13. Roger says:

    This worked perfectly. Thanks!

  14. Denis_67 says:

    Hello guys,

    I have tested successfully the code, but there is an other workaround which delete the filed only for the current pivot and not for the all internal buffer

    the code is
    ActiveSheet.PivotTables(1).DataFields(“MyField”).Parent.PivotItems(ActiveSheet.PivotTables(“Mysheet”).DataFields(“MyField”).Name).Visible = False

    It comes from http://www.pivot-table.com/

    • Sandro says:

      Hi Denis,
      could you better clarify how should I use that code:
      ActiveSheet.PivotTables(1).DataFields(“MyField”).Parent.PivotItems(ActiveSheet.PivotTables(“Mysheet”).DataFields(“MyField”).Name).Visible = False

      You mentioned it comes from http://www.pivot-table.com/ but I can’t find any on it.

      I’m still struggling with calculated field deletion from just current pivot table. As mentioned in several discussions on that subject, Debbra code deletes calculated fields from all pivot tables and when I set it back, all pivot tables will have the same new calculated field, while I just need to reset the one I should delete from the single table and change its formula without changing the others.

      Thanks,
      Sandro

    • Ernie says:

      Yes! This is the way for me. While the deleting and re-adding of calculated fields work, it’s very slow for me. This method works much faster. Thanks!

      ‘ Delete all data fields
      Do Until pvt.PivotFields(“Data”).PivotItems.Count = 0
      pvt.PivotFields(“Data”).PivotItems(1).visible = False
      Loop

    • Jesper says:

      Thx Denis – Works perfect :)

  15. Radka says:

    Perfect! Thank you very much, Debra!

  16. Rodolfo says:

    Thanks, it worked like a charm!

  17. Roland says:

    Thank you!

  18. Laurent Bosc says:

    Like you I want to easily remove data fields (calculated fields or not), like it would be done manually.

    And I finally found this solution (Excel 2010) :

    Set pt = ActiveSheet.PivotTables(“mypivottable”)
    For Each pi In pt.DataPivotField.PivotItems
    pi.Visible = False
    Next

    It works great for me. Let me know if it works for you.

  19. Laurent Bosc says:

    I wanted to easily remove data fields (calculated fields or not), like it would be done manually.

    And I finally found this solution (Excel 2010) :

    Set pt = ActiveSheet.PivotTables(“mypivottable”)
    For Each pi In pt.DataPivotField.PivotItems
    pi.Visible = False
    Next

  20. Dhiren Sharma says:

    I try to run the code, it gives me an error. Object doesn’t support property or method.

Leave a Reply to Jesper Cancel reply

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