[SOLVED] Create a single range from multiple ranges

Create a single range from multiple ranges

I have values in a tabular format. I want to create a range which excludes some rows.

I tried Union. The result was a new range containing only the value of Rng1.

Sub TestUnion()

    Dim Rng1 As Range, Rng2 As Range, NewRng As Range, OutputRng As Range
    
    Set Rng1 = Range("A1:D1")
    Set Rng2 = Range("A3:D5")
    Set NewRng = Union(Rng1, Rng2)
    Set OutputRng = Range("F1:I4")
    
    OutputRng.Value2 = NewRng.Value2

End Sub

Solution

This code should do the job. Please try it.

Sub TestUnion()

    ' list source ranges comma-separated
    Const Sources   As String = "A1:D1,C5,A3:D5"
    Const Target    As String = "F1"

    Dim Src()       As String               ' converted from Sources
    Dim Data        As Variant              ' value of Src(i)
    Dim i           As Long                 ' index of Src()
    Dim Ct          As Long                 ' target column
    Dim Rt          As Long                 ' target row
    
    Src = Split(Sources, ",")
    Rt = Range(Target).Row
    Ct = Range(Target).Column
    
    For i = 0 To UBound(Src)
        Data = Range(Src(i)).Value
        If InStr(Src(i), ":") Then
            Cells(Rt, Ct).Resize(UBound(Data), UBound(Data, 2)).Value = Data
            Rt = Rt + UBound(Data)
        Else
            Cells(Rt, Ct).Value = Data
            Rt = Rt + 1
        End If
    Next i
End Sub

Just set the two constants at the top of the procedure and the code will do the rest. This arrangement isn't strictly necessary but to set it up takes very little time which will be save tenfold if you ever need to make a change.