Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick question on naming a union of ranges
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick question on naming a union of ranges
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 $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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick question on naming a union of ranges
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
naming ranges | Excel Worksheet Functions | |||
naming ranges | Excel Worksheet Functions | |||
printing Union of Ranges | Excel Worksheet Functions | |||
Naming ranges? | Excel Discussion (Misc queries) | |||
Naming Ranges | Excel Discussion (Misc queries) |