14

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?

5

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
  • 1
    I wonder if you can make 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. – Engineer Toast Mar 26 '15 at 17:42
  • 1
    God excel is so old lol – codyc4321 Jan 26 '16 at 15:28
  • Is this still the case? I cannot for my life find out how to 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! – pookie Nov 7 '16 at 15:05
2

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:

Deselect cells

  • Nice find, great answer. – Burgi Mar 31 '16 at 11:06
1

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!

protected by Community Jun 20 '18 at 5:18

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.