clvestin
10-02-2007, 05:08 PM
I'm looking to join/union/merge a column of data from multiple sheets. My first thought was:
Dim rng1 As Range
Dim rng2 As Range
Dim planrange As Range
Set rng1 = Sheets("Aug2007").Range("e5:e34")
Set rng2 = Sheets("Sep2007").Range("e5:e34")
Set planrange = Application.Union(rng1, rng2
but apparently my ranges must be on the same sheet or the union fails.
Any resolution/direction would be appreciated. Ultimately, I would like the data from as much as 12 months to use in format for chart(SeriesCollection) form.
On a side note, I have tried copying the range to an array:
Dim rng1 As Range
Dim rng2 As Range
Dim planrange As Variant
Set rng1 = Sheets("Sep2007").Range("e5:e17")
planrange = rng1.values
This works, but the array elements must be referenced as :
planrange(1,1)
planrange(2,1) etc.
I would have thought a columnar range of data would copy to a 1-d array.
Any advice??
Thanks all
Dim rng1 As Range
Dim rng2 As Range
Dim planrange As Range
Set rng1 = Sheets("Aug2007").Range("e5:e34")
Set rng2 = Sheets("Sep2007").Range("e5:e34")
Set planrange = Application.Union(rng1, rng2
but apparently my ranges must be on the same sheet or the union fails.
Any resolution/direction would be appreciated. Ultimately, I would like the data from as much as 12 months to use in format for chart(SeriesCollection) form.
On a side note, I have tried copying the range to an array:
Dim rng1 As Range
Dim rng2 As Range
Dim planrange As Variant
Set rng1 = Sheets("Sep2007").Range("e5:e17")
planrange = rng1.values
This works, but the array elements must be referenced as :
planrange(1,1)
planrange(2,1) etc.
I would have thought a columnar range of data would copy to a 1-d array.
Any advice??
Thanks all