Selecting multiple ranges using 'Cells' notation

[Pete]

Hi to all in the group
Is it possible to 'set' the range of a variable (defined as Range) to a
multi-range using Cells notation?

This works:
Set RngWeek = Range("E8:K8,U8:AA8")

The macro will I set a row to be the active row
Say it is row 8
Can I set the range using the a command similar to the one below
This doesn't work:
Set RngWeek = Range(Cells(ActiveCell.Row, 5), Cells(ActiveCell.Row, 11),
Cells(ActiveCell.Row, 21), Cells(ActiveCell.Row, 27))

Peter Bircher
Kwazulu-Natal, SA

 

[Tom Ogilvy]

Set RngWeek = Union(Range(Cells(ActiveCell.Row, 5), Cells(ActiveCell.Row,
11)), _
Range(Cells(ActiveCell.Row, 21), Cells(ActiveCell.Row, 27)))
? rngWeek.Address
$E$3:$K$3,$U$3:$AA$3

 

[Bob Phillips]

Hi Peter,

One way

With ActiveSheet
Union(.Range(.Cells(8, 5), .Cells(8, 11)), .Range(.Cells(8, 21),
..Cells(8, 27))).Select
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Pete]

Thanks Tom
Much obliged,

Peter

Set RngWeek = Union(Range(Cells(ActiveCell.Row, 5), Cells(ActiveCell.Row,
11)), _
Range(Cells(ActiveCell.Row, 21), Cells(ActiveCell.Row, 27)))
? rngWeek.Address
$E$3:$K$3,$U$3:$AA$3

 

[Pete]

Thanks Bob
I'll give it a try
Peter

Hi Peter,

One way

With ActiveSheet
Union(.Range(.Cells(8, 5), .Cells(8, 11)), .Range(.Cells(8, 21),
.Cells(8, 27))).Select
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)