VB to select 2 cells simultaneously

[velohead]

Hi All

No doubt the solution is quick, but here goes....

I want to select 2 non-contiguous cells within a macro.
If I specify….
Range("D5,M16").Select
…that works.

If I use the same logic and use the following code (for different cells) then it does not work.

ACA1 = ActiveCell.Address
ActiveCell.Offset(3, 3).Select
ACA2 = ActiveCell.Address

Sheets("Sheet2").Select
Range(ACA1, ACA2).Select

What am I missing please (ie what’s the solution).

Thanks All

 

[RickXL]

Hi,

To do what you were doing, the syntax would look like this:

    ACA1 = ActiveCell.Address
    ActiveCell.Offset(3, 3).Select
    ACA2 = ActiveCell.Address
    Sheets("Sheet2").Select
    Range("" & ACA1 & "," & ACA2 & "").Select

However, you might prefer this:

    Union(ActiveCell, ActiveCell.Offset(3, 3)).Select

Regards,

 

[velohead]

Thanks RickXL, I like the union solution, quite elegant.

Seems a bit strange IMHO that

Range("" & ACA1 & "," & ACA2 & "").Select

works, but

Range(ACA1, ACA2).Select

does not work, as they are effectively the same thing.

 

[RickXL]

They are not quite the same thing.

The one that works has a single string to specify the range and the one that doesn't has two strings separated by commas. Subtle but crucial.


regards,

 

[velohead]

Very subtle indeed, one string versus two strings, I get that.
Thank You

 

[footoo]

Seems a bit strange IMHO that

Range("" & ACA1 & "," & ACA2 & "").Select

Try ;

Range(ACA1 & "," & ACA2).Select

Union(ActiveCell, ActiveCell.Offset(3, 3)).Select

This works provided that Sheet2 is the active sheet at the start. If not :

Dim r$
r = Union(ActiveCell, ActiveCell.Offset(3,3).Address
Sheets("Sheet2").Select
Range(r).Select

 

[velohead]

Yes, the

Range(ACA1 & "," & ACA2).Select

is a bit clearer and still works (as one string)