Ranges Archives - yoursumbuddy

Selection.ListObject and Selection.PivotTable

Posted on October 4, 2015 by Doug Glancy

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:

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

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.

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.

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.

Undo Selections With SelecTracker!

Posted on November 6, 2014 by Doug Glancy

On one of the DDOE posts about International Keyboard Shortcut Day, a commenter known only as VvM asked for ways to undo selections in Excel. You know how it goes. You’ve selected various parts of a huge table to turn them bold or purple or something and you grab one more cell than you wanted. Wouldn’t it be great if you could just unselect it by clicking again, the way you can in just about any other #$;@#%! situation you can imagine. So, you keep clicking and it kind of looks like it worked because the active cell is a different color than the rest of the selection, but no. You’re doomed! You’ve got to start over from scratch. Aargh!

Anyways, the answer is there’s no way to undo selections in the Excel interface proper. You need VBA. And VBA is what you get in the handy form of my newest utility, SelecTracker.

Imagine you’re entered in a contest where you only have to click three cells with high dollar amounts to walk away with fistfuls of cash. Once you start you can’t start over, but you are allowed to use free utilities with goofy names that you downloaded from Excel websites (also with goofy names). In that case SelecTracker could really save your bacon:

That’s right. Just uncheck the offending selection(s) in the handy list and it’s like it never happened.

Other lovely SelecTracker features:

– Start it after your selections are made and you realize you need to unselect
– Doesn’t affect Excel’s Undo stack
– Gives you a better understanding of how Excel selections work

Weird Things I Learned About Selecting in Excel

Try this:

Hold down the Ctrl key and click A1 five times.

Now, in the Immediate window type:
? Selection.Areas.Count and hit Enter

Now enter:
? Selection.Address

So, all those times you tried to unselect by clicking again, you were really just selecting it even more. If only you’d had SelecTracker back then:

If you hold down the Ctrl key and click in cells A1 to A5 one at a time, you’ll get similar, slightly less weird, results. And if you do it and then start up SelecTracker (or vice-versa), you’ll see a different representation of the same results:

So, each selection (with a small “s”) in a Selection is a VBA Area. On the linked MSDN page the Areas definition reads:

The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection.

But clearly that’s within the VBA world where Range("A1:A5") has one Area. But within a Selection (with a capital “S”) it has one Area for each selection you make while holding down the Ctrl key. “Contiguous” doesn’t really enter into it.

This makes the VBA pretty simple. To populate the form’s listbox, I just cycle through the Selection’s Areas and add each one:

With Me.lstSelections
.Clear
For Each SelArea In Selection.Areas
.AddItem SelArea.Address
.Selected(.ListCount - 1) = True
Next SelArea
End With

And to re-select the areas when a listbox item is checked or unchecked:

With Me.lstSelections
For i = 0 To .ListCount - 1
If .Selected(i) Then
NewSelection = NewSelection & .List(i) & ","
End If
Next i
NewSelection = Left(NewSelection, Len(NewSelection) - 1)
ws.Activate
ws.Range(NewSelection).Select
End With

I thought I’d do the above by Unioning the various Areas represented in the listbox. But once you Union them they’re not Areas any more. At least not if there contiguous. So instead I glued the text from the list items together.

(I don’t think other countries use other delimiters than commas, but can’t remember for sure and had no luck searching for it. Please tell me if you know otherwise.)

The rest of the code uses some concepts from UserForm Application-Level Events, Chip Pearson’s code for making forms resizable, and other neat stuff.

Download it and see for yourself.

Thanks VvM, whoever you are!

Why is Deleting Ranges So Backward?

Posted on July 15, 2014 by Doug Glancy

Quite a few questions on Stack Overflow ask about looping through rows and deleting them. The basic question is, “Why is this code only deleting some rows?”:

Set rng = ActiveSheet.Range("A1:A10")
For Each cell In rng
If cell.value = ""
cell.EntireRow.Delete
End If
Next cell

The problem is that the For/Each loop acts like an incrementing For/Next loop, such as:

For i = 1 to 10
If Activesheet.Cells(i,1) = ""
Activesheet.Cells(i,1).EntireRow.Delete
End If
Next i

This means that if it deletes row 2, then row 3 becomes row 2. Meanwhile i increments to 3, so i and the blank cell skip past each other like two contra dancers:

The most basic solution – but not the best one – is to loop backwards through the range. Since you can’t do that with a For/Each loop, you’d use a For/Next and alter the incrementing line to:

For i = 10 to 1 Step -1

The reason that’s not a great answer is it’s slow. It’s much faster to identify the entire range to be deleted and then do so in one swell foop. If the criterion for deletion is blanks, for example, you can use SpecialCells, If SpecialCells doesn’t cover it you can build the range with the Union function (that Tim Williams writes some nice code).

Okay, so, that’s useful stuff. The point of this post is less so, but maybe more interesting. My question is:

My Question

Why does a Range collection behave differently than other collections, such as worksheets or workbooks, when deleting items from it?

For example, the code below, which uses an index, deletes every other worksheet because after the first worksheet is deleted the former Worksheets(2) becomes Worksheets(1) and is skipped over by the loop. By now that should be no surprise:

Sub WorksheetLoopIndex()
Dim i As Long

For i = 1 To ActiveWorkbook.Worksheets.Count
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(i).Delete
Application.DisplayAlerts = True
Next i
End Sub

However, a For/Next loop on worksheets works as you’d want it to, with no skipping. It’s like it figures out in advance which sheets your dealing with and remembers them:

Sub WorksheetLoopForEach()
Dim ws As Excel.Worksheet

For Each ws In ActiveWorkbook.Worksheets
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Next ws
End Sub

The range collection seems to be the oddball – the only one that processes a For/Each just like a For i = 1 to whatever loop.

Interestingly, if you set a separate range to cells within that same loop it adjusts just fine. For example, consider this code, which does a For/Each through a range of cells, and also assigns the last cell in the range to a separate LastCell range. The For/Next skips past cells as before, but the LastCell range moves up one row with each deletion and shifts from A10 to A9, A8 and so on:

Sub DeleteIndividualCellsForEach()
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim LastCell As Excel.Range

Set ws = ActiveSheet
Set rng = ws.Range("A1:A10")
Set LastCell = ws.Range("A10")
rng.Formula = "=Row()"
rng.Value = rng.Value
For Each cell In rng
rng.Interior.ColorIndex = -4142 'none
cell.Interior.ColorIndex = 6 'red
LastCell.Interior.ColorIndex = 3 'yellow
cell.Delete xlUp
Next cell
End Sub

So why don’t cells in the For/Next loop behave as nicely?