Selection.clear does not unselect selection

Question

I want to ~~clear~~ unselect the current selection whenever the user starts "MyMacro".

I'm using the following code:

Sub MyMacro()

    If Not IsSheetExists("Memory") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = "Memory"
    End If

    Sheets("Memory").Visible = xlSheetVisible 'change to xlSheetVeryHidden
    ActiveWorkbook.Sheets("Sheet1").Activate

    ClearAllSheets

    '......
End Sub

Sub ClearAllSheets()
    For Each sh In ActiveWorkbook.Sheets
        sh.Cells.Clear
        sh.Buttons.Delete
        Selection.Clear
    Next sh
End Sub

Why doesn't Selection.Clear ~~clear~~ unselect the current selection?

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Oct 30 '13 at 06:43
Yes, a cell is selected. I don't know why it is not cleared. — user429400, Oct 30 '13 at 06:52
I did not use select in my code. The purpose of the application is to display data to the user when he selects a certain cell. I want to clear this selection when the user re-runs the macro, or simply when he presses a "next button" which I added to the worksheet. — user429400, Oct 30 '13 at 06:54
by `clear the selection`, do you mean unselect, or clearing the contents — sam092, Oct 30 '13 at 06:58
got it. I thought that selection.clear should unselect, while it clear the selected cells , right? so I should simply google "unselect cell"... SORRY you all!!! — user429400, Oct 30 '13 at 07:05
yea see my answer. And AFAIK, there is no such thing as `unselect` in VBA — sam092, Oct 30 '13 at 07:06
Does this answer your question? [Setting selection to Nothing when programming Excel](https://stackoverflow.com/questions/292779/setting-selection-to-nothing-when-programming-excel) — Sandra Rossi, Feb 22 '21 at 09:31

sam092 · Accepted Answer · 2013-10-30T07:12:47.993

5

Alright,

.Clear is meant to clear the contents (& formatting) inside a cell/range, but not unselect

therefore, when you use Selection.Clear, nothing happens

As you have mentioned also, why not just select a dummy cell?

edited Oct 30 '13 at 07:12

answered Oct 30 '13 at 07:05

sam092

1,325
1
8
8

I might be wrong, but AFAIK, there is at least one cell selected at any time on the activesheet – sam092 Oct 30 '13 at 07:18
didn't notice. This is sad :) – user429400 Oct 30 '13 at 07:22

L42 · Answer 2 · 2013-10-30T07:21:45.013

1

Selection.Clear doesn't work since you don't have any selected range.

if you code it like this:

sh.Cells.Select
Selection.Clear

Then it will probably work.
But it is preferable not to use Select.

Update: add this:

Dim rng as range

Set rng = Selection

or in your case:

set rng = sh.Application.Selection

then execute:

Selection.Clear.

it will clear the currently selected range.

edited Oct 30 '13 at 07:21

answered Oct 30 '13 at 06:42

L42

19,427
11
44
68

I don't understand what's going on. I've tried to select a dummy cell and then clear the selection by using Selection.Clear and it does not clear the selection as well... – user429400 Oct 30 '13 at 07:01
again, for `Selection.Clear` to work, you must select something first. if you want to delete the currently selected cells see my update. – L42 Oct 30 '13 at 07:20

score 0 · Answer 3 · edited Oct 08 '19 at 20:36

0

Try this

Application.CutCopyMode = False

it works for me

best regard

edited Oct 08 '19 at 20:36

Hadi GhahremanNezhad

2,377
5
29
58

answered Oct 08 '19 at 20:00

user12184429

11
1

That's the most-voted solution for another same [question](https://stackoverflow.com/a/3794312/9150270), I don't understand why here it's downvoted. Please help me close this question as being a duplicate. – Sandra Rossi Feb 22 '21 at 09:36

score -3 · Answer 4 · answered Apr 22 '15 at 16:47

-3

try this instead

Selection.ClearContents

answered Apr 22 '15 at 16:47

GazzaLDN

5
2
10

It will clear the Contents (Values) of the selected Range. – 4D - Problem Solver Jun 03 '19 at 04:57

Selection.clear does not unselect selection

4 Answers4