A range variable consisting of multiple ranges.

[cpeters5]

Is it possible to do the following

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

- 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

 

[Tom Ogilvy]

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

 

[Bernie Deitrick]

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

 

[cpeters5]

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]

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

 

[Tom Ogilvy]

demo from the immediate window:
set Test1 = union(range("A1:a10"),range("Q5:Q23"))
? test1.Address
$A$1:$A$10,$Q$5:$Q$23
? test1(5,16).Address
$P$5

Notice that to make test1 refer to the union of two ranges, you must use SET

Cells Relative to a range are relative to the upper left corner of the first
range in the union.

But, when building a union, order can be important. If you constructed the
union in reverse you would get a totally different result from the relative
reference:
set Test2 = union(range("Q5:Q23"),range("A1:a10"))
? Test2.Address
$Q$5:$Q$23,$A$1:$A$10
? Test2(5,16).Address
$AF$9

to get P5 you would have to use a different relative value

? Test2(1,0).address
$P$5

So trying to do a relative reference inside a discontiguous union doesn't
make much sense to me.

Dave Peterson was being more specific in that Test1(1,1) identifies a
single cell, A1 (upper left corner of the first range in the union). He
then uses the offset function which is zero based to identify P5 relative to
A1. This is a little redundant, but clearer as to intent. It would
produce the same wrong answer if used with Test2

? test2(1,1).offset(4,15).Address
$AF$9

So overtly refering to the upper left corner does not solve the problem with
the order of the ranges in the union.

Summarize

Offset is zero based. Range("A1").offset(0,0) is A1
the shortcut method is one based. Range("A1")(1,1) is A1

both have their arguments as row offset, then column offset

 

[cpeters5]

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

 

[Tom Ogilvy]

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.