Could this be the stupidest option in Microsoft Excel?

October 11, 2021

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.

retain_automatic

WHY WOULD EXCEL RETAIN DELETED DATA?!

Anyway, I set that option to None:

retain_none

And refreshed the PivotTable. The for-loop now works as expected. 😡