Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
union of named ranges based only on the names of those ranges
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
union of named ranges based only on the names of those ranges
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
union of named ranges based only on the names of those ranges
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
union of named ranges based only on the names of those ranges
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. -- Regards, Tom Ogilvy "sloth" wrote in message ups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative Named Ranges based on worksheet | Excel Worksheet Functions | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming | |||
VBA union of two ranges | Excel Programming |