Unselecting a range

Question

I am currently copying and pasting a range. The problem I am having is that once the macro has run I am left with the range still selected. How do I remove the green rectangle..."deselect"?? The code I am using is:

        For x = 0 To 10
        Range("O" & (34 - x) & ": V" & (34 - x)).Copy
        NextRow = ("O" & (35 - x) & ": V" & (35 - x))
        Range(NextRow).Select
         ActiveSheet.Paste
        Next x

I have tried:

        Application.CutCopyMode = False

But this doesn't seem to work.

I would really appreciate some help! Thanks!

Hint: record a macro doing what you want save the macro, view the source. copy what you need. — xQbert, Jun 09 '16 at 13:17

score 0 · Answer 1 · answered Jun 09 '16 at 13:29

0

Simple solution - avoid the use of Select and Copy or Paste in the first place...

For x = 0 To 10    
    NextRow = ("O" & (35 - x) & ": V" & (35 - x))
    Range(NextRow).Value = Range("O" & (34 - x) & ": V" & (34 - x)).Value
Next x

answered Jun 09 '16 at 13:29

Dave

4,328
2
24
33

score 0 · Answer 2 · answered Jun 09 '16 at 13:29

0

Put the line at the beginning of your macro:

Set objInitSel = ActiveSheet.Selection

And at the end of the macro:

objInitSel.Select

answered Jun 09 '16 at 13:29

omegastripes

12,351
4
45
96

score 0 · Answer 3 · edited May 23 '17 at 11:51

As @Dave already mentioned, you're best avoiding the use of the .Select property.

This question and it's answers gives a good explanation of how to avoid using select and the reasons behind why you should avoid using select.

Primarily: inefficiency and causation of errors.

The 2 main alternatives to using select is:

Directly use the .Copy and .PasteSpecial methods with a range (reducing four lines of code into one):

Range("O" & (34 - x) & ": V" & (34 - x)).copy Range("O" & (35 - x) & ": V" & (35 - x))

Assign the values of one range to another range:

Range("O" & (35 - x) & ": V" & (35 - x)) = Range("O" & (34 - x) & ": V" & (34 - x))

Unselecting a range

3 Answers3