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" wrote in message
oups.com...
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.