Quick question on naming a union of ranges

[adam_kroger]

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
$30"), Range("$AE$33:$AH$37"), _ Range("$AE$40:$AH$44"), Range("$AE
$47:$AH$51"), Range("$AE$54:$AH$58"), Range("$AE$61:$AH$65"), _
Range("$AE$68:$AH$72"), Range("$AE$75:$AH$79"), Range("$AE$82:$AH
$86"), Range("$AE$89:$AH$93"), _
Range("$AE$96:$AH$100"), Range("$AE$103:$AH$107"), Range("$AE$110:$AH
$114"), _
Range("$AE$117:$AH$121"), Range("$AE$124:$AH$128"), Range("$AE$131:$AH
$135"), _
Range("$AE$138:$AH$142"), Range("$AE$145:$AH$149"), Range("$AE$152:$AH
$156"), _
Range("$AE$159:$AH$163"), Range("$AE$166:$AH$170"), Range("$AE$173:$AH
$177"), _
Range("$AE$180:$AH$184"), Range("$AE$187:$AH$191"), Range("$AE$194:$AH
$198"), _
Range("$AE$201:$AH$205"), Range("$AE$208:$AH$212"), Range("$AE$215:$AH
$219"), _
Range("$AE$222:$AH$226"), Range("$AE$229:$AH$233"), Range("$AE$236:$AH
$240"), _
Range("$AE$243:$AH$247"), Range("$AE$250:$AH$254"), Range("$AE$257:$AH
$261"), _
Range("$AE$264:$AH$268"), Range("$AE$271:$AH$275"), Range("$AE$278:$AH
$282"), _
Range("$AE$285:$AH$289"), Range("$AE$292:$AH$296"), Range("$AE$299:$AH
$303"), _
Range("$AE$306:$AH$310"), Range("$AE$313:$AH$317"), Range("$AE$320:$AH
$324"), _
Range("$AE$327:$AH$331"), Range("$AE$334:$AH$338"), Range("$AE$341:$AH
$345"), _
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

 

[Guest]

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

 

[adam_kroger]

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