I have some code that creates a journal entry import file based on a pivot table. It loops through the PivotItems and reads the DataRange.Value2 property. It’s nothing fancy or particularly complicated, but it gets the job done. Or it used to. You know that old problem with pivot items hanging around?. Yeah, that got me.

Unable to get the DataRange property of the PivotItem class

Once I realized that lCnt equaled 41 and I only had 40 GL Codes, I immediately knew the problem. But how to fix it. I checked the locals window to see if there was anything identifiable about the ghost pivot item.

It looks like when the RecordCount property is zero, it’s a pivot item that can be ignored. I considered code like this:

I also read Debra’s site on the matter and considering just setting the Retain Items to “None” at design time. The problem with that is that I may redo the pivot table or reuse the code on a different table and forget to set that property. I ended up setting that property in code. Instead of checking RecordCount in the loop, I just set the MissingItemsLimit property to xlMissingItemsNone before I refresh and it gets rid of any stray pivot items.