On an earlier post I received some very helpful advice regarding the
merging of ranges from various worksheets. I have posted the code
below. I am now wondering if it is also possible to capture the values
that are in the adjacent column of each of these ranges and have them
transferred adjacent to the resulting consolidated range.

I hope that makes sense to someone.

David



Dim coll As Collection


Sub main()
Set coll = New Collection
Dim r As Range
Set r = Sheets("Sheet1").Range("A1:A10")
Call Builder(r)
Set r = Sheets("Sheet2").Range("B1:B10")
Call Builder(r)
Set r = Sheets("Sheet3").Range("C1:C10")
Call Builder(r)
Set r = Sheets("Sheet1").Range("B1")
Call Displayer(r)
Set coll = Nothing
End Sub


Sub Builder(r As Range)
Dim arr
arr = r
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
End Sub


Sub Displayer(r As Range)
MsgBox coll.Count
For i = 1 To coll.Count
r.Value = coll.Item(i)
Set r = r.Offset(1, 0)
Next
End Sub