Excel VBA - Dynamically supply ranges to Union method -

user has defined named ranges print in excel.

i reading these ranges vba array. there way supply range names union method set non-contiguous print ranges.

for example, like: activesheet.pagesetup.printarea = union(range(array(1)), range(array(2))).address

the number of ranges held in array can vary. i've experimented looping through array , building string variable, no success.

any appreciated.

you'll have substitute actual range names or objects in statement, here how use union function set printarea:

sub foo()     dim setup pagesetup     set setup = activesheet.pagesetup      setup.printarea = union(range("myrange1"), range("myrange2")).address end sub 

what i'm looking method construct union statement using range names held in array

ok, use above method , custom function construct union in loop:

sub foo()     dim setup pagesetup     dim rangearray(1) range      set setup = activesheet.pagesetup     set rangearray(0) = range("myrange1")     set rangearray(1) = range("myrange2")      setup.printarea = getunion(rangearray) end sub function getunion(arr variant) string     dim itm variant     dim ret range     each itm in arr         if not ret nothing             set ret = union(ret, itm)         else             set ret = itm         end if     next     if not ret nothing         getunion = ret.address     else         getunion = ""  'may cause error...     end if end function