Updating Multiple Range

[daniel22bg]

Hi there,

I need to have e multiple range that is updating itself with each spinning of the loop. Something like this (skipping the unimportant parts):

Dim UnionRng, SlectRng as Range
 
Set UnionRng = Nothing
     
For j = 1 To 50
     SelectCol = j + 20
     Set SelectRng = Range(Cells(2, SelectCol), Cells(100, SelectCol))
     Set UnionRng = Union(UnionRng, SelectRng)
Next j

I have a problem with this part "Set UnionRng = Union(UnionRng, SelectRng)". I know that this is possible im MATLAB but it seems that VBA is not supporting such functionality. Is there any way that I can get around it? Thanks

 

[Jon von der Heyden]

You can't use union on a range that doesn't already exist, so first we have to set it before attempting a union.

[COLOR=blue]Sub[/COLOR] test()
 
[COLOR=blue]Dim[/COLOR] UnionRng, SelectRng [COLOR=blue]As[/COLOR] Range
 
[COLOR=blue]Set[/COLOR] UnionRng = [COLOR=blue]Nothing[/COLOR]
 
[COLOR=blue]For[/COLOR] j = 1 [COLOR=blue]To[/COLOR] 50
     SelectCol = j + 20
     [COLOR=blue]Set[/COLOR] SelectRng = Range(Cells(2, SelectCol), Cells(100, SelectCol))
     [COLOR=blue]If[/COLOR] UnionRng [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
        [COLOR=blue]Set[/COLOR] UnionRng = SelectRng
    [COLOR=blue]Else[/COLOR]
        [COLOR=blue]Set[/COLOR] UnionRng = Union(UnionRng, SelectRng)
     [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]Next[/COLOR] j
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[daniel22bg]

Thanks. That help a lot