View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
James Ravenswood James Ravenswood is offline
external usenet poster
 
Posts: 143
Default Merging Multiple Named Ranges

On Nov 19, 4:14*pm, wrote:
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.