PivotCache.MissingItemsLimit property (Excel)

Returns or sets the maximum quantity of unique items per PivotTable field that are retained even when they have no supporting data in the cache records. Read/write XlPivotTableMissingItems.



expression A variable that represents a PivotCache object.


This property can be set to a value between 0 and 32,500. If an integer less than zero is specified, this is equivalent to specifying xlMissingItemsDefault. Integers greater than 32,500 can be specified but will have the same effect as specifying xlMissingItemsMax.

The MissingItemsLimit property only works for non-OLAP PivotTables; otherwise, a run-time error can occur.


This example determines the maximum quantity of unique items per field and notifies the user. The example assumes that a PivotTable exists on the active worksheet.

Sub CheckMissingItemsList() 
 Dim pvtCache As PivotCache 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 ' Determine the maximum number of unique items allowed per PivotField and notify the user. 
 Select Case pvtCache.MissingItemsLimit 
 Case xlMissingItemsDefault 
 MsgBox "The default value of unique items per PivotField is allowed." 
 Case xlMissingItemsMax 
 MsgBox "The maximum value of unique items per PivotField is allowed." 
 Case xlMissingItemsNone 
 MsgBox "No unique items per PivotField are allowed." 
 End Select 
End Sub

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.