Looping through PivotItems that Don’t Exist

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:

Set pt = wshExport.PivotTables(1)
pt.RefreshTable
wshExport.Range("A1").EntireColumn.AutoFit
Set pf = pt.PivotFields("GLAccount")

ReDim aOutput(1 To pf.PivotItems.Count + 1)

For Each pi In pf.PivotItems
If pi.RecordCount > 0 Then
lCnt = lCnt + 1
aRow(1) = "N"
aRow(2) = Format(pi.DataRange.Value2, "0.00")
aRow(3) = "N"
aRow(4) = "C"
aRow(5) = UCase(Replace$(wshExport.Range("rngDescription").Value, ",", "_"))
aRow(6) = pi.LabelRange.Value2
aRow(7) = Format(wshExport.Range("rngPeriod").Value, "00")
aRow(8) = "JE" & Format(wshExport.Range("rngJournal").Value, "00")
aRow(9) = wshExport.Range("rngSource").Text
aRow(10) = Format(wshExport.Range("rngTranDate").Value, "mm/dd/yyyy")
aRow(11) = Format(wshExport.Range("rngTranDate").Value, "dd")
aRow(12) = Format(wshExport.Range("rngTranDate").Value, "mm")
aRow(13) = Format(wshExport.Range("rngTranDate").Value, "yyyy")

aOutput(lCnt) = Join(aRow, ",")

dTotal = dTotal + pi.DataRange.Value
End If
Next pi

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.

Set pt = wshExport.PivotTables(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.RefreshTable
wshExport.Range("A1").EntireColumn.AutoFit
Set pf = pt.PivotFields("GLAccount")

ReDim aOutput(1 To pf.PivotItems.Count + 1)

3 thoughts on “Looping through PivotItems that Don’t Exist

  1. Thanks for that. I’ve been bitten by that one before and was unaware that the fix was so simple.

  2. Excellent tip. I always used On Error Resume Next to trap these – admittedly a less elegant solution. I’ll have to switch to this.

Leave a Reply

Your email address will not be published. Required fields are marked *