Selection.ListObject and Selection.PivotTable

Selection.ListObject and Selection.PivotTable

In recent posts about using VBA to work with filters I’ve used ActiveCell.ListObject to test whether I’m dealing with a filtered table or a filtered range. At one point it occurred that maybe I should be looking at the Selection’s ListObject instead. For various reasons that doesn’t work, but it made me wonder how Selection.ListObject gets evaluated. For example, what if it spans two tables? And, while we’re at it what about Selection.PivotTable?

To be clear, everything said about Selection in this post actually applies to the more general Range object. It’s just that Selection is the range I’m interested in.

Selection.PivotTable

Let’s start with Pivot Tables. Excel’s help actually says exactly how Range.PivotTable is handled:

Returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the [Selection].

So,

Selection.PivotTable

returns an error with the selection below because its upper-left corner is outside the pivot:

pivot selection

But this selection will return the pivot table because the upper-left cell of the selection intersects the pivot table:

pivot selection 2

Selection.ListObject

The rules for ListObjects, or at least what I’m guessing are the rules, are different. There’s nothing I can find in Help, so here’s what I made up:

Returns the ListObject intersected by the Selection. If more than one ListObject intersects the Selection, the ListObject that was created earliest is returned.

None of the pivot table’s “upper-left corner” stuff. If the selection intersects the table, the table is returned. So below

Selection.Listobject

returns Table3.

listobject selection 1

When the Selection intersects multiple ListObjects, it looks to me like Selection.ListObject returns the one that was created earliest. With the selection below, that means that it returns Table1.

listobject selection 2

In Conclusion

Now I have even more reasons to never use Selection.ListObject or Selection.PivotTable! They’re both quirky, and I don’t think I could expect users to know that if they select more than one table, they’ll get the oldest one (or for that matter, that they’d get any one). And for a pivot table, it would be kind of fussy to insist the upper-left corner of the selection be in the pivot table.

I’m sticking with ActiveCell. I think that’s the way Range.PivotTable and Range.Selection should have been designed as well. In other words, like Range.PivotTable does, only using ActiveCell instead of the upper-left corner.

Speak Your Mind

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

To post code, do this: <code> your vba here </code>