CODEWITHSUNDEEP
vbaexcel
Curt
Curt

Reputation: 1214

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.

Upvotes: 35

Views: 319853

Answers (18)

NoobInLifeGeneral
NoobInLifeGeneral

Reputation: 21

What if you tried to use a more direct script?

I had the same issue with some code:

Columns("C:C").Select
Selection.EntireColumn.Hidden = True

Very easy, i know. But now i use:

Columns("C:C").EntireColumn.Hidden = True

Since it doesnt select anything, you dont have the green outline anywhere. I dont know if there is a specific function for your use, but if there is this would be the easiest option i think.

Cant you try something like:

Range("NewGraph").value = Range("OldGraph").value

ps. noob here so i dont know if it makes sense but after learning stuff here im just trying to help some others.

Upvotes: 0

Michael Yeh
Michael Yeh

Reputation: 1

Do statement as below:

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

Upvotes: 0

BOBY
BOBY

Reputation: 11

Sub MyFunc()

    Range("B6").Select

    Selection.Locked = True

End Sub

Upvotes: 1

Phil
Phil

Reputation: 11

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

Upvotes: 1

dko
dko

Reputation: 361

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!

Upvotes: 0

RHH1095
RHH1095

Reputation: 99

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

Application.CutCopyMode = True

Upvotes: 6

JustinMT
JustinMT

Reputation: 13

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.

Upvotes: 1

user5514699
user5514699

Reputation:

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

Upvotes: 2

Garreth Tinsley
Garreth Tinsley

Reputation: 1

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

Upvotes: 0

user3032537
user3032537

Reputation: 111

Select any cell and turn off cutcopymode.

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

Upvotes: 11

iDevlop
iDevlop

Reputation: 25262

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

Upvotes: 0

TheAxeman
TheAxeman

Reputation: 135

Just use

SendKeys "{ESC}"

thereby cancelling your selection.

Upvotes: 11

Jaaahn
Jaaahn

Reputation: 1

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.

Upvotes: 0

David
David

Reputation: 511

Application.CutCopyMode = False

Upvotes: 51

Miguel
Miguel

Reputation: 77

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.

Upvotes: 6

shahkalpesh
shahkalpesh

Reputation: 33474

Cells(1,1).Select

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

Upvotes: 27

Madhur Kashyap
Madhur Kashyap

Reputation: 11

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

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 3

Related Questions