Excel – How to append or combine Ranges


Im doing an app with Excel VBA and I need to copy some ranges from different Worksheet in a variable.
All the ranges have the same number of columms but different number of rows. I was thinking on making like a matrix. Just append the UsedRange from every Sheet one down the other… but no way

For i = 1 To wbDataFile.Sheets.Count

    Set wsCPDataFile = wbDataFile.Sheets(wbDataFile.Sheets(i).Name)
    If Not DataRng Is Nothing Then
        Set DataRng=  DataRng (Append??)
    ' the first valid cell becomes rng2
        Set DataRng = wsCPDataFile.UsedRange
    End If

Next I
Data = DataRng

Best Solution

Use Union. Like so:

Sub jzz()
Dim rng As Range

Set rng = Range("A1")

Set rng = Union(rng, Range("A2"))

End Sub
Related Question