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

When you introduce deleting a column, you open a whole new can of worms.

set rng = Range("A:A,Q:Q")
? rng.Address
$A:$A,$Q:$Q
? columns(2).Delete
True
? rng.Address
$A:$A,$P:$P
columns(1).Delete
? rng.Address
$O:$O

Test out some of your ideas in the immediate window and perhaps you will
find a solution.

--
Regards,
Tom Ogilvy


wrote in message
ups.com...
Thanks Tom,

Insightful lesson!
If I understand you correctly, relative references on a union of ranges
will not work if the position these ranges (in relative to each other)
changes.

E.g. if column B to P are deleted, the two ranges become

"A1:A10" and "B5,B23"

and the reference identification, or offset will refer to a wrong cell,
or in this example, invalid.

If this is so and there is no way around it, I will have to change my
approach and not using union.

Thanks,
pac