How do I set selection to Nothing when programming Excel using VBA?

Question

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.

Curt

Answer 1

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

Answer 2

If possible, you could select something innocuous.

Answer 3

Cells(1,1).Select

It will take you to cell A1, thereby canceling your existing selection.

Answer 4

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.

Answer 5

Application.CutCopyMode = False

Answer 6

Hi David,

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

Answer 7

Why not just select the first series?