Join multiple ranges into single range in using UNION Excel VBA

Union

Function takes range as argument and join them into single range and return a range. Union method takes minimum two arguments need to be supplied to achieve sensible result and maximum 30 range arguments can be supplied to this function.

Syntax

UNION(Arg1 range, Arg2 range……..Arg30 range)

Code example

Public Sub UnionExample()
    'Declare worksheet object
    Dim oWorkSheet As Worksheet
    'Bind sheet reference
    Set oWorkSheet = ThisWorkbook.Worksheets("Sheet4")
    
    'declare first range object
    Dim oRange_one As Range
    'declare second range object
    Dim oRange_two As Range
    
    'Bind first range
    Set oRange_one = oWorkSheet.Range("C4:D4")
    
    'Print first range address
    Debug.Print "Range one address : " & oRange_one.Address
    
    'bind second range
    Set oRange_two = oWorkSheet.Range("C5:D5")
    
    'Print second range address
    Debug.Print "Range two address : " & oRange_two.Address
    
    'Declare final range object
    Dim oFinalRange As Range
    
    'Union both ranges (first and second) and build final range object
    Set oFinalRange = Union(oRange_one, oRange_two)
    
    'Print final range address
    Debug.Print "Final union range address : " & oFinalRange.Address
    
    'Memory cleanup
    Set oRange_one = Nothing
    Set oRange_two = Nothing
    Set oFinalRange = Nothing
    Set oWorkSheet = Nothing
End Sub

Output

Range one address : $C$4:$D$4
Range two address : $C$5:$D$5
Final union range address : $C$4:$D$5

Leave your comments, If like please do subscribe to our blogs to keep your self upto date.

Share on Facebook

Tweet