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.
I’ve been using this to adjust my row count:
If pt.PivotFields("Values").Orientation = xlColumnField Then lColumnFields = lColumnFields - 1
On Error GoTo 0
…but as per your previous post, I’m screwed if someone changes the name of the Values field. Will amend.
Another problem is that, for me at least, the bug only appears when the Values field is the only column field. I didn’t notice this until later. So, for example, if there’s a Years column field, the bug goes away.
Ignore my code…that was for some other problem.
How did you create this PivotTable? If I create a new one manually, it has the word ‘Values’ above your Units datafield, and I don’t get your offset problem. But if I use your existing PivotTable, I do get your offset problem.
If you created it programmatically, perhaps you’re using an old method.
Ah. If you save the file as an .xls and then reopen it and create a new PivotTable, you get some old kind of layout as shown in your screenshot in which your offset problem rears its ugly head. So it’s a compatibility problem between old and new.
Jeff, that’s not the case.That’s a table in an xlsx with format changed to tabular and most formatting cleared. In any event, I see the same issue with a brand new unmodified pivot table in a brand new xlsx.
Ah, found the issue. Got confused by a side about differences between versions when creating PivotTables manually. This bug is version related, however…and is evident when you create PivotTables with VBA, like I was when I wrote the above comment.
I almost always create my PivotTables programmatically, using my InstantPivot routine published at http://dailydoseofexcel.com/archives/2014/02/12/instant-pivot-just-add-water/
That code uses the PivotCaches.Create method, and gives you an Excel 2007-style PivotTable that has subtle differences than the PivotTable you get if you manually create a PivotTable. For one thing, you get a slightly different layout: The PivotTable column headings take up 2 rows, not one, and the first row has nothing but the word ‘Values’ in it. For another thing, the PivotItem.DataRange addressing works correctly.
But if creating a PivotTable manually, the macro recorder shows that Excel now uses the PivotCache. CreatePivotTable method, in which that Values word doesn’t appear, and in which the PivotItem.DataRange addressing works incorrectly.
I suspect when they introduced the new method they forgot to amend the PivotItem.DataRange method, which likely had some kind of logic to offset everything by 1 in the event that multiple data fields were present (meaning that ‘Values’ text would appear in an additional row).
In other words, it’s a bug in later versions than Excel 2007 IMHO.
Out of curiosity, what is it that you are trying to achieve in using that PivotITem.DataRange stuff? Is this for creating dynamic ranges tied to PivotTables?
Hey Jeff, that’s interesting. Thanks for ferreting it out.
No, it’s not for creating dynamic pivot table ranges: we’ve both already done that! It’s for my per-item conditional formatting routine. I’m now on my third approach to that and have bypassed PivotSelect and intersections of data ranges, and moved on to something else, which I hope to post soon.
Cool. My revised PivotNames routine generates all kinds of names for interesting intersections within PivotTables. I’m in the process of making it shareable, and when I do I’ll flick a file your way in case my approach is useful.
I’ve encountered this issue as well. One of the things I noticed that seemed to cause the issue was when DisplayFieldCaptions = False. When I turned this to True, the LabelRange returned correctly.
In addition to having DisplayFieldCaptions = True, you’ll also want to ensure ShowValuesRow = True
Thanks Michael