Merging Multiple Named Ranges

[dave.cuthill]

I am trying to figure out a method to merge about 10 named ranges. The
ranges vary in length but are all one column wide. The ranges are in
different worksheets but all within the same workbook. I am using
Excel 2007.

I know that there are duplicates within all the ranges but I only want
to retain one instance of the duplicate values and any of the unique
values from each range. How can this be done? I would prefer to
automate it with vb since I have 20 groups of these named ranges.

David

 

[James Ravenswood]

I am trying to figure out a method to merge about 10 named ranges. The
ranges vary in length but are all one column wide. The ranges are in
different worksheets but all within the same workbook. I am using
Excel 2007.

I know that there are duplicates within all the ranges but I only want
to retain one instance of the duplicate values and any of the unique
values from each range. How can this be done? I would prefer to
automate it with vb since I have 20 groups of these named ranges.

David

Hi David:

Merging ranges usually means using Union() to combine them into a
single range. This does not work across worksheets. That is why this
code fails:

Sub marine()
Dim r1 As Range, r2 As Range, r As Range
Set r1 = Sheets("Sheet1").Range("A1:A2")
Set r2 = Sheets("Sheet2").Range("B5:B6")
Set r = Union(r1, r2)
End Sub

The best you can do is to create an array containing the non-
duplicated contents of the ranges.

 

[dave.cuthill]

Hi David:

Sorry but could you explain the array method? can you still use the
union method with an array?

 

[James Ravenswood]

Sorry but could you explain the array method? can you still use the
union method with an array?






- Show quoted text -- Hide quoted text -
call the Displayer
- Show quoted text -

Here is an example. We have three different ranges on three different
sheets. We call the Builder macro three times. The Builder macro
accumulates a Collection of all the items in the sheets. Finally we
call the Displayer macro. The Displayer macro just dumps the items
back into one of the sheets in single collumn:


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

 

[dave.cuthill]

Thank you that works amazingly - now I just have to figure out how it
works.

 

[dave.cuthill]

If I wanted to also capture the value of the cells in the column to
the right of the ranges from the various sheets - how could that be
done? I would want to display this value to the right of the summary
range that is generated by the Display sub.

David