PivotItem.DataRange Off By One Row Bug

PivotItem.DataRange Off By One Row Bug

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:

PivotIItem.DataRange Offby One

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:

Function GetPivotItemOffsetBugCorrection(pvt As Excel.PivotTable) As Long
'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:

Set pvt = pvtItem.Parent.Parent
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.

Filter by Selected Cell’s Value – Bug With Blanks

Filter by Selected Cell’s Value – Bug With Blanks

I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.

The Issue

In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.

Testing

I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.

How to Replicate It

Create a table like this one. It can actually be just one column:

table

Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).

filter menu

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

preview

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

filter dialog

What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.

You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:

translation

Workarounds

The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.

A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.

So, Did Everybody Else Already Know This?

I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.

I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.