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.

I had a surprising issue like that, check in the Pivot Table options (right click on the table), in Data tab, you should find Retain items deleted from the data source, choose None for Number of items to retain per field. Then refresh all should be in order! ;) (that error drove me mad for hours!^^) — R3uK
Actually it was already to None, I unchecked everything, selected max, saved, closed, checked again, and set to None, and now its working properly... Thx for the tip, I've never heard about this ! i.stack.imgur.com/oFSOT.jpg — Jaggana

R3uK R3uK · Accepted Answer · 2016-10-11T10:28:18

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!^^)

Excel VBA - PivotItems returns inexesting value

1 Answers