[vba] Setting selection to Nothing when programming Excel

When I create a graph after using range.copy and range.paste it leaves the paste range selected, and then when I create a graph a few lines later, it uses the selection as the first series in the plot. I can delete the series, but is there a more elegant way to do this? I tried

Set selection = nothing

but it won't let me set selection. I also tried selection.clear, but that just cleared the last cells that were selected, and still added an extra series to the plot.

Application.CutCopyMode = False

Select any cell and turn off cutcopymode.

Range("A1").Select
Application.CutCopyMode = False

I do not think that this can be done. Here is some code copied with no modifications from Chip Pearson's site: http://www.cpearson.com/excel/UnSelect.aspx.

UnSelectActiveCell

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

UnSelectCurrentArea

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

You can simply use this code at the end. (Do not use False)

Application.CutCopyMode = True

Selection(1, 1).Select will select only the top left cell of your current selection.

You could set the Application.ScreenUpdating = False and select a cell out of view and then set the .ScreenUpdating to true. This would at least not show any selected cells in the current view.

Selection(1, 1).Select will select only the top left cell of your current selection.

I do not think that this can be done. Here is some code copied with no modifications from Chip Pearson's site: http://www.cpearson.com/excel/UnSelect.aspx.

UnSelectActiveCell

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

UnSelectCurrentArea

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

Just use

SendKeys "{ESC}"

thereby cancelling your selection.

None of the many answers with Application.CutCopyMode or .Select worked for me.

But I did find a solution not posted here, which worked fantastically for me!

From StackExchange SuperUser: Excel VBA “Unselect” wanted

If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.

Sub NoSelect()
   With ActiveSheet
   .EnableSelection = xlNoSelection
   .Protect
   End With
End Sub

As soon as the sheet is unprotected, the cursor will activate a cell.

Hope this helps someone with the same problem!

Sub MyFunc()

    Range("B6").Select

    Selection.Locked = True

End Sub

Do statement as below:

ActiveSheet.Cells(ActiveWindow.SplitRow+1,ActiveWindow.SplitColumn+1).Select

I had this issue with Excel 2013. I had "freeze panes" set, which caused the problem. The issue was resolved when I removed the frozen panes.

Select any cell and turn off cutcopymode.

Range("A1").Select
Application.CutCopyMode = False

There is a way to SELECT NOTHING that solve your problem.

1. Create a shape (one rectangle)
2. Name it in Shapes Database >>> for example: "Ready"
3. Refer to it MYDOC.Shapes("Ready") and change the visibility to False

When you want that Excel SELECT NOTHING do it:

MYDOC.Shapes("Ready").visible=True
MYDOC.Shapes("Ready").Select
MYDOC.Shapes("Ready").visible=False

This HIDE the selection and nothing still selected in your window PLUS: The word "Ready" is shown at the Left Top in your Sheet.

I do not think that this can be done. Here is some code copied with no modifications from Chip Pearson's site: http://www.cpearson.com/excel/UnSelect.aspx.

UnSelectActiveCell

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

UnSelectCurrentArea

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

If using a button to call the paste procedure,

try activating the button after the operation. this successfully clears the selection

without

• having to mess around with hidden things
• selecting another cell (which is exactly the opposite of what was asked)
• affecting the clipboard mode
• having the hacky method of pressing escape which doesn't always work

HTH

Sub BtnCopypasta_Worksheet_Click()
   range.copy
   range.paste
BtnCopypasta.Activate
End sub

HTH

In Excel 2007, a combination using select and CutCopyMode property, it is possible to reset all the selections. It worked for my use case.

Application.CutCopyMode = xlCopy
ActiveSheet.Range("A" & lngRow).Select

Regards Madhur

Sub MyFunc()

    Range("B6").Select

    Selection.Locked = True

End Sub

Do statement as below:

ActiveSheet.Cells(ActiveWindow.SplitRow+1,ActiveWindow.SplitColumn+1).Select

In Excel 2007, a combination using select and CutCopyMode property, it is possible to reset all the selections. It worked for my use case.

Application.CutCopyMode = xlCopy
ActiveSheet.Range("A" & lngRow).Select

Regards Madhur

Application.CutCopyMode = False

I do not think that this can be done. Here is some code copied with no modifications from Chip Pearson's site: http://www.cpearson.com/excel/UnSelect.aspx.

UnSelectActiveCell

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

UnSelectCurrentArea

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

You could set the Application.ScreenUpdating = False and select a cell out of view and then set the .ScreenUpdating to true. This would at least not show any selected cells in the current view.

You can simply use this code at the end. (Do not use False)

Application.CutCopyMode = True

I had this issue with Excel 2013. I had "freeze panes" set, which caused the problem. The issue was resolved when I removed the frozen panes.

Tried all your suggestions, no luck , but here's an idea that worked for me select a cell out of your selection range (say AAA1000000) and then select the A1 again

Range("AAA1000000").Activate

Range("A1").Activate

Guy

If you select a cell in an already selected range, it will not work. But, Selecting a range outside the original selection will clear the original selection.

'* The original selection *' ActiveSheet.range("A1:K10").Select

'* New Selections *' Activesheet.Range("L1").Select

'* Then *' Activesheet.Range("A1").Select

If using a button to call the paste procedure,

try activating the button after the operation. this successfully clears the selection

without

• having to mess around with hidden things
• selecting another cell (which is exactly the opposite of what was asked)
• affecting the clipboard mode
• having the hacky method of pressing escape which doesn't always work

HTH

Sub BtnCopypasta_Worksheet_Click()
   range.copy
   range.paste
BtnCopypasta.Activate
End sub

HTH

There is a way to SELECT NOTHING that solve your problem.

1. Create a shape (one rectangle)
2. Name it in Shapes Database >>> for example: "Ready"
3. Refer to it MYDOC.Shapes("Ready") and change the visibility to False

When you want that Excel SELECT NOTHING do it:

MYDOC.Shapes("Ready").visible=True
MYDOC.Shapes("Ready").Select
MYDOC.Shapes("Ready").visible=False

This HIDE the selection and nothing still selected in your window PLUS: The word "Ready" is shown at the Left Top in your Sheet.

If you select a cell in an already selected range, it will not work. But, Selecting a range outside the original selection will clear the original selection.

'* The original selection *' ActiveSheet.range("A1:K10").Select

'* New Selections *' Activesheet.Range("L1").Select

'* Then *' Activesheet.Range("A1").Select

Just use

SendKeys "{ESC}"

thereby cancelling your selection.

Tried all your suggestions, no luck , but here's an idea that worked for me select a cell out of your selection range (say AAA1000000) and then select the A1 again

Range("AAA1000000").Activate

Range("A1").Activate

Guy