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!
That ‘5 areas’ thing is strange. I wonder if there’s an advantage to “fixing it up” in the userform code so that it makes it more like what people expect.
I think Excel’s behavior is odd, but I suppose if people use this tool they’ll figure it out and change what they expect. Plus I like the cleanness of the code and if I try to obscure what’s really happening, and then have to code for when they de-select, etc., it will get all complicated and be more like work 🙂 instead of this fun little behind-the-scenes quirk this tool brings to light..
Pretty deep, huh?
Pingback: Excel Roundup 20141110 « Contextures Blog
Pingback: Daily Dose of Excel » Blog Archive » No you CANNOT have more of the same
Very nice, Doug. I’ll definitely peak under the bonnet. As per my post over at DDOE at http://dailydoseofexcel.com/archives/2014/11/10/no-you-cannot-have-more-of-the-same/, I tried another approach to the same ends…automatically deselect any cells that the user selects twice, and set up an application-level event so this always happens.
I like how you do this with Events.
I made something long ago using an input box (Application.InputBox) and will try to post here. The funny thing is the Autofilter stuff. Can’t remember why it’s there but I must have had a reason.
Here goes.
Dim rCell As Range
Dim CurrentRange As Range
Dim DeselectRange As Range
Dim NewRange As Range
Dim FilterRange As Range
On Error Resume Next
Set CurrentRange = Selection
Set DeselectRange = Application.InputBox("Choose the cells that you want to deselect." _
& vbNewLine & vbNewLine & "Keep the Ctrl key depressed to select multiple areas.", _
"Deselect Cells Within Range", , , , , Type:=8)
Set FilterRange = ActiveSheet.AutoFilter.Range
ActiveSheet.AutoFilterMode = False
For Each rCell In CurrentRange.Cells
If Intersect(rCell, DeselectRange) Is Nothing Then
If NewRange Is Nothing Then
Set NewRange = rCell
Else
Set NewRange = Union(NewRange, rCell)
End If
End If
Next rCell
FilterRange.AutoFilter
NewRange.Select
Set CurrentRange = Nothing
Set DeselectRange = Nothing
Set FilterRange = Nothing
Set NewRange = Nothing
On Error GoTo 0
End Sub
Hey Andrew, thanks.
That works for me. I wonder if you turned off the AutoFilter to keep ranges contiguous.
Can’t remember Doug, that may have been it… should have written a comment in my code…