Pivot Table Calculated Field Removed with Excel VBA

If you’re working with pivot table calculated fields in Excel VBA programming, or trying to use the Orientation property for data fields, you might encounter some problems. Here’s how I managed to work around an error that occurs when you try to remove a calculated field from the pivot table layout with Excel VBA.

Strange Results for Orientation Property

I encountered the first oddity with Orientation while working with pivot table field names. I wanted to see a bit of information about each pivot field, so I wrote some Excel VBA pivot field code, to show the caption, source name, location (orientation), position, sample data, and a formula (calculated fields).

The code worked well, but the Orientation property gave some strange results for the fields in the Values area. Instead of listing the field location as Data, they were shown as Hidden. At the top of the screen shot below, you can see the field names circled in the pivot table layout, but the list says they’re hidden.

PivotFieldListCode01

Removing the Data Fields

I experimented 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 weren’t removed.

Instead of changing the calculated field Orientation to xlHidden, Excel displayed the error message “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”

PivotFieldListCode03

On the Excel worksheet, 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 a macro 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 was delete the calculated field, instead of trying to remove it from the layout. That approach is okay if you want to permanently remove the calculated field, but 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 had trouble removing calculated fields from the pivot table layout, please let me know if this code helps you.

Update: If multiple pivot tables share the same pivot cache, calculated field is removed from all. To prevent accidental deletion from other pivot tables, go to this article for sample code: Check for Shared Pivot Cache

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

___________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Calculated Field, Excel Pivot Tables, Excel VBA, Pivot Table. Bookmark the permalink.

12 Responses to Pivot Table Calculated Field Removed with Excel VBA

  1. Bill B says:

    Hi Debra

    I did have cause to use your code. I had a pivot table with two calculated fields, growth per month and the annualized equivalent growth. The emphasis was on the Annualized growth and I only wanted that to show, but I still wanted the MonthlyGrowth to show on the list of pivot fields as a reference.

    Getting exactly that using automation, with the hidden problem you highlight here, would waste many hours and cause me to tear my hair out. Your routine worked perfectly. The only surprise was that I had to reformat the new pivotfield and change it’s ‘Sum of’ name. In fact, I just moved that formatting down to below your code as it was all getting deleted anyway.

    Only one problem – I couldn’t format and change the name for the xlhidden field. In fact, if the user manually clicks off the xldatafield and later puts it back, the formatting and name are lost. (Is there coding for events on pivot tables?) Anyway, I can live with those problems.

    Thanks a bunch. Love this site – almost as much as I love pivot tables.

  2. Steve Kiwi says:

    The codes works well, but I discovered one problem with it. If you’re using the calculated field in more than one pivot table, when you delete it with this code, it gets deleted from all of those pivot tables, but when you add it back it only gets added back to the one referenced as pt. The other pivots then no longer contain that calculated field and may give errors depending on how they’re being used. I had to add some extra code to make sure it got added back to each of the other pivots too, so that my pivots would all work correctly.

  3. Rod says:

    Hi.

    I am getting the same error with regular (not calculated) fields. I think that it has to do with trying to hide a PivotField that is already hidden (ie, not in the current pivot).

    For Each pt In Worksheets(“RightColumn”).PivotTables
    ‘Clear filters
    pt.PivotFields(“unit_sname”).Orientation = xlHidden

    In this example, the unit_sname field is available but not showing as a Row, Column or Page field. From what limited experimenting I have done, trying to hide it when already hidden seems to be why I get the error message.

    So I would need to write a line of code that, using some kind of IF statement, checks first to see if the field is in the pivot table view or not.

    Does that make sense?

    Thanks!

  4. ASHISH JAIN says:

    Did you copied this article from Debra’s contextures or she copied it from yours ?
    http://blog.contextures.com/archives/2010/09/08/remove-pivot-table-calculated-field-with-excel-vba/

    • Ashish, usually I only post the pivot table articles here, but occasionally I post a similar article on my Contextures blog too. I thought this sample code could would be of interest to readers of both my blogs.

  5. Rory Archibald says:

    Debra,
    You should be able to hide the item if you refer to it as a datafield and thereby as an item of the Values field:

    With ActiveSheet.PivotTables(1).DataFields(“Sum of NewTax”)
    .Parent.PivotItems(.Name).Visible = False
    End With

    for example.

  6. Trevor says:

    Nice one Debra, that worked for me.
    I prefer to use a single line version:

    ActiveSheet.PivotTables(1).DataFields(“Sum of NewTax”).Parent.PivotItems(ActiveSheet.PivotTables(1).DataFields(“Sum of NewTax”).Name).Visible = False

  7. Dror says:

    Hi,
    This is indeed a wonderful site with great learning tools.
    The specific topic of removing calculated fields really helped me, but i have the same problem described above by Steve Kiwi :calculated fields in other PT in other sheets are being deleted as well.
    Is there a solution/workaround to prevent the deletions in other tables in other sheets or get the fields back into the respective PT?

    TIA

  8. I’ve posted new code that will cancel the macro is other pivot tables share the same pivot cache.

    http://www.pivot-table.com/excel-pivot-tables/check-for-shared-pivot-cache

  9. Thanks a ton for the above code.

    If some one want to delete all Data fields & Calculated fields.. I am using the below code.


    Sub HidePFields()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfNew As PivotField
    Dim strSource As String
    Dim strFormula As String
    Set pt = Sheets("Agent - Daywise").PivotTables("PivotTable4")
    For Each pf In pt.CalculatedFields
    strSource = pf.SourceName
    strFormula = pf.Formula
    pf.Delete
    Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
    Next pf

    With pt
    .ManualUpdate = True
    For Each PfNew In .DataFields
    PfNew.Orientation = xlHidden
    Next PfNew
    .ManualUpdate = False
    End With

    Set pt = Nothing
    End Sub

  10. robin says:

    Thanks for the code, it was going mad trying to hide calc’d fields, figured it was something like this.

  11. PaCenar says:

    Thanks Nadeem for your code.
    Here you have a small variation of it to avoid as far as possible the PivotTableĀ“s blinking

    Sub HidePFields()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfNew As PivotField
    Dim strSource As String
    Dim strFormula As String

    Set pt = Sheets(“Agent – Daywise”).PivotTables(“PivotTable4″)

    For Each pf In pt.CalculatedFields
    If pf.ShowingInAxis Then GoTo Continue
    Next pf

    Exit Sub

    Continue:

    For Each pf In pt.CalculatedFields
    If pf.ShowingInAxis Then
    strSource = pf.SourceName
    strFormula = pf.formula
    pf.Delete
    Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
    End If
    Next pf

    With pt
    .ManualUpdate = True
    For Each pfNew In .DataFields
    pfNew.Orientation = xlHidden
    Next pfNew
    .ManualUpdate = False
    End With

    Set pt = Nothing
    End Sub

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>