PivotCache.MissingItemsLimit (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. Possible return values are xlMissingItemsDefault - The default number of unique items per PivotField allowed, xlMissingItemsMax - The maximum number of unique items per PivotField allowed (32,500) for a pre-Excel 2007 PivotTable, xlMissingItemsMax2 - The maximum number of unique items per PivotField allowed (1,048,576) for PivotTables in Excel 2007 and later, xlMissingItemsNone - No unique items per PivotField allowed (zero).

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.

ActiveWorkbook.PivotCaches(1).MissingItemsLimit = xlMissingItemsDefault