I don't think this is an excel bug, I think it's a 'feature'. ;-)
Re: @AMissico, there is no problem in excel hiding all of the fields in a pivot table, but he may be talking about items - you can't hide the last item in a pivot field.
This is the code I routinely use to do what you are trying to do. These macros were developed on Excel 2002 & 2003. I don't hide CalculatedFields, I delete them.
' Hide all fields.
' @param ThePivotTable to operate upon.
Sub HidePivotFields(ByVal ThePivotTable As PivotTable)
Dim pField As PivotField
For Each pField In ThePivotTable.CalculatedFields
pField.Delete
Next pField
For Each pField In ThePivotTable.PivotFields
pField.Orientation = xlHidden
Next pField
Set pField = Nothing
End Sub
' Removes FieldName data from ThePivotTable
Sub HideField(ByVal ThePivotTable As PivotTable, _
ByVal FieldName As String)
If FieldExists(ThePivotTable, FieldName) = True And _
CalculatedFieldExists(ThePivotTable, FieldName) = False Then
ThePivotTable.PivotFields(FieldName).Orientation = xlHidden
End If
End Sub
' Returns True if FieldName exists in ThePivotTable
'
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function FieldExists(ByVal ThePivotTable As PivotTable, _
ByVal FieldName As String) As Boolean
Dim pField As PivotField
For Each pField In ThePivotTable.PivotFields
If pField.SourceName = FieldName Then
FieldExists = True
Exit For
End If
Next pField
Set pField = Nothing
End Function
' Checks if the field FieldName is currently a member of the
' CalculatedFields Collection in ThePivotTable.
' @return True if a CalculatedField has a SourceName matching the FieldName
' @return False otherwise
Function CalculatedFieldExists(ByVal ThePivotTable As PivotTable, _
ByVal FieldName As String) As Boolean
Dim pField As PivotField
CalculatedFieldExists = False
For Each pField In ThePivotTable.CalculatedFields
If pField.SourceName = FieldName Then
CalculatedFieldExists = True
End If
Next pField
Set pField = Nothing
End Function
' Returns a Pivot Field reference by searching through the source names.
'
' This function is a guard against the user having changed a field name on me.
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function GetField(ByVal ThePivotTable As PivotTable, _
ByVal FieldName As String) As PivotField
Dim pField As PivotField
For Each pField In ThePivotTable.PivotFields
If pField.Name <> "Data" Then
If pField.SourceName = FieldName Then
Set GetField = pField
Exit For
End If
End If
Next pField
Set pField = Nothing
End Function
' Counts the number of currently visible pivot items in a field.
' @param ThePivotItems the collection of pivot itemns in a field.
' @return the count of the visible items.
Function PivotItemCount(ByVal ThePivotItems As PivotItems) As Long
Dim pItem As PivotItem
Dim c As Long
For Each pItem In ThePivotItems
If pItem.Visible = True Then c = c + 1
Next pItem
PivotItemCount = c
Set pItem = Nothing
End Function
' Hides a single pivot item in a pivot field, unless it's the last one.
' @param FieldName pivot field containing the pivot item.
' @param ItemName pivot item to hide.
Sub HidePivotItem(ByVal ThePivotTable As PivotTable, _
ByVal FieldName As String, _
ByVal ItemName As String)
Dim pField As PivotField
Set pField = GetField(ThePivotTable, FieldName)
If Not pField Is Nothing Then
If PivotItemCount(pField.PivotItems) > 1 Then
pField.PivotItems(ItemName).Visible = False
End If
End If
Set pField = Nothing
End Sub