union of named ranges based only on the names of those ranges

[sloth]

I have a list of names of ranges in column A. How can I create a name
object that refers to the union of all the cells in each of the listed
named ranges?

For example, in column A I have listed: Vol, ARPU, Rev, Exp. Each has
10 values. I would like to create a range called Data that consists of
all 40 cells.

Right now, I'm doing something like this, which works:

Part 1:
Set rng = Union (Range(Application.Names!Vol),
Range(Application.Names!Rev), ...)

.... the code goes on to write values of the union range to Sheet1 and
then activate the first cell in the contiguous range

Part 2:
Dim sDataAddress as String
sDataAddress = ActiveCell.Address
ThisWorkbook.Names.Add Name:="Data", RefersTo:="Sheet1!" & sDataAddress

So what I'm trying to do is in Part 1 not have to refer to each name in
the code, but have the union run based on the names of listed named
ranges in column A.

I'm *guessing* it could be accomplished by creating a string array
based on the lists of names and then looping thru this array and using
the & operator to fill in the arguments for the union.

Also, is there a better way to create the name than what I used in Part
2?

Much appreciation for any help.

 

[Peter T]

Simply define a named formula to refer to your named ranges, eg

Name: Data
Refersto: =Vol,ARPU,Rev,Exp

or
Activeworkbook.Names.add "Data", "=Vol,ARPU,Rev,Exp"

this should be OK but be aware of the max 255 string limit in the refersto
string.

Regards,
Peter T

 

[sloth]

Thanks Peter. I did that and it appeared to work, but when I tried to
assign the new name range (data) to a range variable (rng1) I got the
following error:

Method 'Range' of object '_Global' failed

Any thoughts?

 

[Tom Ogilvy]

dim rng as Range
set rng = Range("Data")

if you are doing it in a sheet module and the named range refers to cells on
another sheet then you will need to qualify it with that sheet name

set rng = Worksheets("ABC").Range("Data")

or
set rng = Thisworkbook.Names("Data").ReferstoRange

of course this assumes that the name Data actually refers to a range.