This week I ran into a pivot table VBA issue I’ve never noticed before. When a pivot table has more than one data field, referring to a PivotItem.DataRange returns a range one row down from where it should be. Below you can see that the PivotItem.DataRange address is one row off and that the selection is below the pivot table:
If the pivot table has only one data field, e.g., if I get rid of “Sum of Total” above, the issue goes away.
I found one reference to this by Macro Marc on SO, but nothing else on the web. It seems like it would be a well-known thing though, especially if it’s been around for a while.
I’m curious if anybody knows whether this has been reported as a bug. I noticed it on my home computer running Office 365 Pro Plus. I’d be interested to hear if it’s on other versions.
My Workaround
In my very limited testing it seems like there isn’t a similar issue for PivotFields. So one idea is to compare the first row of a pivot field against the first row of its first pivot item and use the difference, if any, to offset the PivotItem.DataRange back to where it should be. However, I’m not sure that my concept of “first” will always be the same as Excel’s. Anyways I’m using this function:
'Only occurs if the pivot table has more than one data field
If pvt.DataFields.Count = 1 Then
Exit Function
End If
GetPivotItemOffsetBugCorrection = pvt.VisibleFields(1).DataRange.Row - _
pvt.VisibleFields(1).VisibleItems(1).DataRange.Row
End Function
Then I use it like this in places where I refer to a pivot item’s data range:
PivotItemOffsetBugCorrection = GetPivotItemOffsetBugCorrection(pvt)
For Each cell In pvtItem.DataRange.Offset(PivotItemOffsetBugCorrection)
Yuck!
If you’ve got a good solution for dealing with this, or any info, please leave a comment.
