macro for selecting non-adjacent cells

[EricBB]

how can i edit the below line so that row no. will be a variable. i.e. if i
select cell "A5" the "3" will become 5.

Range("A3,C3,E3,H3,K3").Select

thanks for any help.

 

[CmK]

Range("A" & ActiveCell.Row & ",C" & ActiveCell.Row & ",E" & ActiveCell.Row &
",H" & ActiveCell.Row & ",K" & ActiveCell.Row).SELECT

 

[Dave Peterson]

I'd use:

Dim iRow As Long
Dim myRng As Range

iRow = 5 'or activecell.row
With ActiveSheet
Set myRng = Intersect(.Rows(iRow), .Range("a1,c1,e1,h1,k1").EntireColumn)
End With
myRng.Select

 

[Ron de Bruin]

Use this for the row where the activecell is

Cells(ActiveCell.Row, 1).Range("A1,C1,E1,H1,K1").Select

 

[CmK]

Obvioucly i am new to this just curious how can you refer to a range object
from a cells method
Isnt range higher in the object hierachy

Thanks in advance

 

[EricBB]

thanks a lot CMK, Dave, Ron.

 

[Ron de Bruin]

Hi CmK

It is an unusual way of using range I agree.
I believe I saw Tom O posted it first a few years ago.
When you enter for example “Cells†you will see that range is in the Intellisense
Dropdown after you press the. (Dot after cells)

And you see that it is working OK