Could this be the stupidest option in Microsoft Excel?
For the past hour or so I have been troubleshooting a problem with a Visual Basic script in Excel. It iterates through PivotItems in a PivotTable:
Set p_table = ActiveSheet.PivotTables("Audit") Set p_items = p_table.PivotFields("Primary Location").PivotItems For Each p_item In p_items Debug.Print p_item.Name Next p_item
The for-loop keeps returning items that are no longer in the source data, and I couldn’t work out why. Turns out, there’s an option in PivotTable Options: Retain items deleted from the data source.
WHY WOULD EXCEL RETAIN DELETED DATA?!
Anyway, I set that option to None:
And refreshed the PivotTable. The for-loop now works as expected. 😡