Is it possible to do the following

- There are two names in a sheet:
test1 = "A1:A3" and test2 = "D12:D16"

- I would like to define a single range variable:
Dim testing As Range
Set testing = Union
(ThisWorkbook.Names"test1").RefersToRange,
ThisWorkbook.Names("test2").RefersToRange)

I know that this does not work, but is there a way to fix it ?
Thanks,
pac

Dim testing As Range
Set testing = Union(Range("Test1"),Range("Test2"))
Testing.Select

Test1 and test2 must be on the same sheet.

(although Your original should work as well. )

--
Regards,
Tom Ogilvy





wrote in message
oups.com...
Is it possible to do the following

- There are two names in a sheet:
test1 = "A1:A3" and test2 = "D12:D16"

- I would like to define a single range variable:
Dim testing As Range
Set testing = Union
(ThisWorkbook.Names"test1").RefersToRange,
ThisWorkbook.Names("test2").RefersToRange)

I know that this does not work, but is there a way to fix it ?
Thanks,
pac

pac,

If you used named ranges:

Dim testing As Range
Set testing = Union(Range("test1"), Range("test2"))
MsgBox testing.Address

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
Is it possible to do the following

- There are two names in a sheet:
test1 = "A1:A3" and test2 = "D12:D16"

- I would like to define a single range variable:
Dim testing As Range
Set testing = Union
(ThisWorkbook.Names"test1").RefersToRange,
ThisWorkbook.Names("test2").RefersToRange)

I know that this does not work, but is there a way to fix it ?
Thanks,
pac

Thanks both Bernie and Tom for the answer. It gave me a jump start for
the day. made a lot of progress.

However, I was trying to ad a little bit of bells and whistles to the
code but was stuck with relative position from the range.


More explicitly, if given

Test1 = union(range("A1:a10"),range("Q5:Q23"))

How do I refer to the cell P5 using relative position to Test1?
Thanks,
pac

You could also use:

set test1 = range("a1:a10,q5:q23")
(if you were working with addresses.)

I'd use something like:

Dim test1 As Range
Dim test2 As Range
Set test1 = Range("a1:a10,q5:q23")
Set test2 = test1.Cells(1, 1).Offset(4, 15)
MsgBox test2.Address



" wrote:

Thanks both Bernie and Tom for the answer. It gave me a jump start for
the day. made a lot of progress.

However, I was trying to ad a little bit of bells and whistles to the
code but was stuck with relative position from the range.

More explicitly, if given

Test1 = union(range("A1:a10"),range("Q5:Q23"))

How do I refer to the cell P5 using relative position to Test1?
Thanks,
pac

--

Dave Peterson