Excel vba: error hiding calculated field in Pivot table

Question

I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder's code also halts on the last line. I googled the error message, without serious result.

Sub PrRemove()
    'remove PR
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("MyPivot")
    pt.PivotFields("MyField").Orientation = xlHidden   '<- here is the error
End Sub

The same code works fine if MyField is a normal field (not a calculated one).
I am using Excel 2007 with SP2.
Any clue ?

EDIT on 17 June 2010: I also tried using pt.DataFields instead of pt.PivotFields, with exactly the same behaviour. The error message says "Unable to set the orientation of the PivotField class".

Solution 1

Well, I will give you the confirmation you need. It seems using the Orientation property on a "Calulated Field" just does not work, and I would have to agree this is a bug and not a common "usage" error. I was able to duplicate "hiding/showing" the field without having to remove ("Delete") the calculated field. This allows the user to physically drag the calculated field from the field list after you have progammatically "hidden" the field. This is not a bad solution because it duplicates the user-interface. (Using Excel 2003.)

'2009.09.25 AMJ
'work around for
'   1004, Unable to set the Orientation property of the PivotField class
'when setting orientation property to hidden of calculated field, as in
'   ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Field1").Orientation = xlHidden

Public Sub Hide()
'hide the data without removing the calculated field
'   this allows the user to physically drag the
'       calculated field from the field list once we
'       have "hidden" it programmatically.
'   if we use the "delete" method, the field is removed
'       from the pivot table and the field list

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField
    Dim oPI As PivotItem

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.DataFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    Set oPI = oPF.DataRange.Cells(1, 1).PivotItem
    'oPI.DataRange.Select
    oPI.Visible = False

End Sub

Public Sub Show()
'show just reads the pivot field to the data fields

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.PivotFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    oPT.AddDataField oPF

End Sub

---

[original answer] Most likely you cannot hide this item because it is the last visible item. Instead, try removing it.

Solution 2

after much hair pulling i have found a workaround. if you add more than one pivot field (calculated or otherwise) excel creates a grouped field called Values. you can set the orientation property of PivotField("Values") to xlHidden and it bullets both fields. So if you want to remove a calculated field, just add a non-calculated field, set PivotField("Values").orientation to xlHidden and you're done.

nobody said it would be pretty...

Solution 3

With ActiveSheet.PivotTables("PivottableName").PivotFields("Values")
    .PivotItems("CalcFieldName").Visible = False
End With

Solution 4

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

Solution 5

Here is a little workaround I discovered today, again not very elegant but at least it doesn't need much code, it will hide ALL the fields and you will need to reshow the ones you want after:

objTable.DataPivotField.Orientation = xlHidden

You may run into an error if excel for some reason thinks the datapivotfield is empty, but to fix this just add in another field as a datafield right before the above statement. Also make sure its the letter l not the number 1 in xlHidden vba's default font has them looking very very similar.

Happy Coding

View more solutions

Comments

[lanartri]

It is not the last visible. I can do that by hand, record it. It just does not replay. All I found was this thread, suggesting this is a known bug: social.msdn.microsoft.com/Forums/en-US/isvvba/thread/…

[lanartri]

Delete works, but that deletes the calculated field, which I can't re-add then.

[AMissico]

I don't understand why you can re-add it?

[lanartri]

I have tried with both. I had already done this with other fields with no problem. It only bugs with the calculated field. ANd the strange think is that recorded macro does not replay !!

[lanartri]

AMissico: yes, you're right, I could re-add it. But I am looking first for a "clean" solution, before going to the workaround. I really think this is an Excel bug, infact, but I'd like to have come confirmation OR learn something :-)

[DaveParillo]

By any chance, when recording the macro, did you select exactly the cells to be in the pivot table, or did you select the entire sheet or entire columns? I have noticed that when you select the range in this way the macro records incorrectly.

[lanartri]

@DaveParillo: I recorded ok. I had already coded for 2 "real" fields" successfully, and I copy/pasted the same code. The problem just happens with the calculated field.

[lanartri]

@AMissico: good idea, I didn't think about that, I lost faith in M$ support long time ago, but I'll give them a try.

[lanartri]

Good to know. It seems to be the only solution to that Excel "inconsistency".

[lanartri]

I gave you the point since I don't see other solution except waiting a year or 2 for m$ to react.

[AMissico]

"you can't hide the last item in a pivot field." Isn't that what I said?

[DaveParillo]

Yes, but the question was about a PivotField, not a PivotItem. So I thought your reference to 'this item' was referring to @Patrick Honorez's code, so I was just trying to clarify.

[AMissico]

Well, I will give you some confirmation. I was not able to find a "clean" solution. It seems using the Orientation property on a Calulated Field just does not work, and I would have to agree this is a bug and not a "usage" error. I was able to duplicate "hiding" the field without having to remove ("Delete") the calculated field, and have updated my answer.

[AMissico]

@DaveParillo, Got it. It would have been clearer if I said, "you cannot hide this pivot field because it must have at least one visible item. For reference, in code, this condition generates an error. In Excel's user-interface, the user is prompted with "At least one item must be selected for display in this field." if they unselect all items for a field.

[lanartri]

Jeremy B: this is not really a calculated field. If you already have Units field and Cost field, then having an Amount field = Units * Costs, that is a calculated field. But thanks anyway for the trick.

[lanartri]

that looks really interesting! I'll check out and come back to you. Thanks for the info.

[lanartri]

your remark about datafields was right, but unfortunately that does not solve the problem on calculated fields (see question edits)

[Alinboss]

Hy, i don't understand, datafields are calculated fields, i applied this code on a huge variety of pivot tables and it clears all the datafields/pivotfields/calculatedfields. Pay attention it doesn't delete the pivot table only clears it. Did you tried my code and it didn't work?

[lanartri]

Thanks ! I'll give it a try in a few days. It's never too late :-)

[mbomb007]

Please do not post a code-only answer. Provide an explanation on how it works or why it is a solution. Otherwise, it's more suitable to be a comment once you have the reputation required. stackoverflow.com/help/whats-reputation

[lanartri]

looks interesting. I'll give it a try. Thanks

[Przemyslaw Remin]

Can you please explain how to use your line of code? Definitely it looks like taken from larger whole of code.

[Asger]

Yes, this simple code line is good for hiding all previously used Datafields, but it only works, if you already have at least 2(!) datafields. If you only have one or none, then you'll get an error. So in fact, a loop over all datafields is better.

[nir]

Thanks, this is exactly what i neded. i have a code to switch pivot datafield values from item to calculated item and back, the code of ".Orientation = xlHidden " worked fine on items but gave the error message on calculated items. now its working perfect.