Selecting multiple ranges using 'Cells' notation

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

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

--
Regards,
Tom Ogilvy

"Pete" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Thanks Tom
Much obliged,

Peter

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Pete" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Thanks Bob
I'll give it a try
Peter

"Bob Phillips" <[email protected]> wrote in message
news:O%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>