One can select multiple Excel cells (either contiguously or not) using the keyboard shortcut Ctrl+Click.
How do you deselect one or more of these previously-selected cells?
By using the SHIFT and/or the CTRL keys you can select non-contiguous ranges. However, if you select a cell or area by mistake, there is no built in way to remove that from the selection without losing the entire selection and having to start over. This page describes to VBA procedures, UnSelectActiveCell and UnSelectCurrentArea that will remove the Active Cell or the Area containing the Active Cell from the current selection. All other cells in the Selection will remain selected.
Your best bet would be to add these to your Personal Macro workbook so that they are available to all open workbooks in Excel.
This procedure will remove the Active Cell from the Selection.
Sub UnSelectActiveCell()
Dim R As Range
Dim RR As Range
For Each R In Selection.Cells
If StrComp(R.Address, ActiveCell.Address, vbBinaryCompare) <> 0 Then
If RR Is Nothing Then
Set RR = R
Else
Set RR = Application.Union(RR, R)
End If
End If
Next R
If Not RR Is Nothing Then
RR.Select
End If
End Sub
This procedure will remove the Area containing the Active Cell from the Selection.
Sub UnSelectCurrentArea()
Dim Area As Range
Dim RR As Range
For Each Area In Selection.Areas
If Application.Intersect(Area, ActiveCell) Is Nothing Then
If RR Is Nothing Then
Set RR = Area
Else
Set RR = Application.Union(RR, Area)
End If
End If
Next Area
If Not RR Is Nothing Then
RR.Select
End If
End Sub
UnSelectActiveCell
faster if you union by ranges and only loop through the cells if Not Intersect(Area,ActiveCell) Is Nothing
. It might not be the case if the Intersect
function is so much slower than the StrComp
function that it can't offset the reduction in calls to Union
.
Commented
Mar 26, 2015 at 17:42
deselect
a cell. It's absolutely ridiculous! I am selecting every 2nd column and if I make a mistake, I must start over? That's %&*ing insane!
A more robust way for deselecting multiple cells is described in this ExtendOffice article. It does include an extra prompt, but you can deselect an arbitrary number of cells/selections at once (instead of deselecting only the active cell or area)
I'm posting the script here, with a small usability improvement (conditionally removed the redundant first prompt from the original post):
Sub DeselectCells()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim result As Range
xTitleId = "Deselect Cells"
Set InputRng = Application.Selection
If InputRng.Count <= 1 Then
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
End If
Set DeleteRng = Application.InputBox("Delete Range", xTitleId, Type:=8)
For Each rng In InputRng
If Application.Intersect(rng, DeleteRng) Is Nothing Then
If result Is Nothing Then
Set result = rng
Else
Set result = Application.Union(result, rng)
End If
End If
Next
result.Select
End Sub
To use it, you make a selection, invoke the DeselectCells
macro (which is best to be saved in your Personal macro book and assigned to a shortcut) and select the cells to be deselected in the popup that appears:
It is now possible to unselect with CTRL a cell selected by mistake. It's a new built in function in Office 365 or recent versions. Finally!