Tried:
Set SETUP!Year = Union(Range("SUPER!Half1"), Range("SUPER!Half2"))
and
Set SETUP!Year = Union(Range("Half1"), Range("Half2"))

where Half1 and Half2 were valid ranges but I got an object error.
worked as:
ActiveWorkbook.Names.Add Name:="SUPER!Half2",
RefersTo:=Application.Union(Range("Half1"), Range("Half2"))

On Jul 29, 3:16 am, Mike H wrote:
Hi,

Do it in 2 halves. You code was a bit too lengthy for me but this
demonstrates the method:-

Sub joinranges()
ActiveSheet.Names.Add Name:="MyRange1", RefersTo:="=$A$1:$B$10"
ActiveSheet.Names.Add Name:="MyRange2", RefersTo:="=$c$1:$d$10"
Set bigrange = Union(Range("MyRange1"), Range("MyRange2"))
End Sub

MyRange1 & Myrange2 could have 30 elements each and then be combined into
'BigRange'

Mike



" wrote:
I need to name a union of 52 ranges into a range named "SUPER!
Year" (the same series of cells on other worksheets will have the name
Year as well) I tried using:

ActiveWorkbook.Names.Add Name:="SUPER!Year1",
RefersTo:=Application.Union(Range("$AE$5:$AH$9"), _
Range("$AE$12:$AH$16"), Range("$AE$19:$AH$23"), Range("$AE$26:$AH

<<<big 'ole snip_

Range("$AE$348:$AH$352"), Range("$AE$355:$AH$359"), Range("$AE$362:$AH
$366"))

But it will not handle 52 (only 30). I saw a thread a few days ago
that had my answer (in fact it is what got me this far and the other
ranges I needed named) but now I can't find it again, and apparently
my notes were not a good as I thought. What would the VBA code be to
do either of the followinng?

name the selected cells in a worksheet (name is local to that
worksheet)
or
Name a union of named ranges (QUPER!Qtr1, SUPER!Qtr2, SUPER1Qtr3,
SUPER!Qtr4)

Thanks- Hide quoted text -

- Show quoted text -