Remove missing PivotField items

 2010-05-08    PivotTables    0    166

Sometimes when you refresh a PivotTable you might experience that the new datasource has one or more fields that no longer contain one or more items that was present in the previous datasource. These "ghost" PivotField items will still show up in the PivotFields dropdown menu. With the macro below you can update a PivotTable so that it no longer lists these missing PivotField items:

Sub RemoveMissingPivotFieldItems(pt As PivotTable)
' use it like this from another macro: RemoveMissingPivotFieldItems(ActiveSheet.PivotTables(1))
Dim lngMIL As Long
    If pt Is Nothing Then Exit Sub

    With pt.PivotCache
        lngMIL = .MissingItemsLimit
        If lngMIL <> xlMissingItemsNone Then
            .MissingItemsLimit = xlMissingItemsNone
            On Error Resume Next
            .Refresh
            On Error GoTo 0
            .MissingItemsLimit = lngMIL
        Else
            .Refresh
        End If
    End With
End Sub