macro for selecting non-adjacent cells

E

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.
 
C

CmK

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

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
 
R

Ron de Bruin

Use this for the row where the activecell is

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

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
 
R

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
 
Top