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:
.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:
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!