Excel VBA - PivotItems returns inexesting value

Question

In an Excel Workbook, I have "static" pivot table on a sheet, that is based on data from another sheet.

I'm refreshing the data on my data sheet (thank you captain Obvious !), then I want to show ALL the items, exept the blank one, so I'm running throw all the PivotItems to set them to visible, and, at the end, unselecting the blank one :

i = 1

ThisWorkbook.Sheets("TCD").PivotTables(i).PivotFields("CODETAT").ClearAllFilters
ThisWorkbook.Sheets("TCD").PivotTables(i).PivotCache.MissingItemsLimit = xlMissingItemsNone
For Each PvI In ThisWorkbook.Sheets("TCD").PivotTables(i).PivotFields("CODETAT").PivotItems
    PvI.Visible = True
Next

ThisWorkbook.Sheets("TCD").PivotTables(i).PivotFields("CODETAT").PivotItems("(blank)").Visible = False

At the last occurs of my loop, on the 4th PivotItems, I have an error of execution '1004' (I'll translate it from french, it may me some errors, sorry) "Impossible to define the property Visible of the class PivoItem", so I checked a few things :

?ThisWorkbook.Sheets("TCD").PivotTables(i).PivotFields("CODETAT").PivotItems.count
 4 

for x = 1 to 4 :

?ThisWorkbook.Sheets("TCD").PivotTables(i).PivotFields("CODETAT").PivotItems(x)
(blank)
SFT
ACQ
TEP

It look like I have 4 items in my Pivot Table, but

And also, when I check my datas, I only have 2 different stats :

So where does this 4th PivotItems' element is coming from, and how can I get ride of it ? Thank you.

Answer 1

I had a surprising issue like that, you need to check in the Pivot Table options :

• Right click on the pivot table,
• Select Pivot Table options
• Go in Data tab
• Find Retain items deleted from the data source
• Choose None for Number of items to retain per field!
• Then refresh! All should be OK now! ;)

(that thing drove me mad for hours!^^)