Difference between Select and Activate

[Texas Longhorn]

Hi all,

I'm a newbie to VBA, and I was hoping someone could explain to me the difference between Select and Activate. For example, all four of the following do the same thing:

Sub test()

Range("B2").Select
Range("B2").Copy Destination:=Selection.Offset(0, 1)

End Sub

Sub test()

Range("B2").Activate
Range("B2").Copy Destination:=ActiveCell.Offset(0, 1)

End Sub

Sub test()

Range("B2").Select
Range("B2").Copy Destination:=ActiveCell.Offset(0, 1)

End Sub

Sub test()

Range("B2").Activate
Range("B2").Copy Destination:=Selection.Offset(0, 1)

End Sub

Could someone please explain if there is any reason to use one or the other (or neither)?

Thanks,
Bill

 

[Mike Blackman]

Hi Bill,

In all honesty, my advice would be to use neither, if you remove the line with the select or Activate on and run it you will see that it still does what you ask it without the inefficiency of having to Select/Activate the cell.

 

[RobMatthews]

I'm also interested in the answer to the question..

 

[jindon]

Select can select multiple objects.

Activate can activate only one object.

Sub example()
Range("a1:a10").Select
MsgBox "Selection.Address = " & Selection.Address & vbLf & _
           "ActiveCell.Address = " & ActiveCell.Address
End Sub

 

[Texas Longhorn]

Thanks for the replies. Mikey B - I've read a few threads that also discuss omitting the Select or Activate to improve efficiency. My question then is what is the most efficient way to have the copied cell be pasted in a position relative to the copied cell? That is, if I don't select or activate in the first line then Selection.Offset(0,1) won't work. What would you recommend?

Thanks,
Bill

 

[gauntletxg]

If your code is not dependent on your user making their own selection and then executing your subroutine, you could use something like

With Range("B2")
    .Copy Destination:=.Offset(0, 1)
    ' Other stuff you want to do with Range("B2")
End With

 

[RoryA]

Sub test()
 
Range("B2").Activate
Range("B2").Copy Destination:=Selection.Offset(0, 1)
 
End Sub

Could someone please explain if there is any reason to use one or the other (or neither)?

Thanks,
Bill

You need to be very careful when using Activate on a range - it is better to use Select if you must actually select it. Try running the above in the following two situations and see what happens:
1. Select A1 then run the code.
2. Select a1:C10 then run the code.

Totally different scenarios!!
HTH

 

[Texas Longhorn]

gauntletxg - thanks for the tip; I have changed some code to mimic your example. rorya - thanks for the example. I tried it and completely see your point...a good demonstration.

Thanks,
Bill

 

[guruxcel]

This is the best explanation that i have read

http://analysis-reporting.blogspot.com/2009/10/vba-difference-between-sheetsselect-and.html

 

[Texas Longhorn]

Thanks guruxcel...nice explanation.