Union of several ranges

Posted by Ron Dionne on November 01, 2001 8:00 AM

I'd like to take several ranges from different sheets
and join them together into one big range. The union
method seems to be what I need to use, but I get an
error when trying to use it with this code:

For j = 1 To finalWB.Worksheets.Count
With finalWB.Worksheets(j)
Set bigRange(j) = .Range(.Cells(RowOffset + 1, 3), .Cells(NumRows, 3))
End With
Next

For m = 1 To NumFiles
Set totalRange = Application.Union(totalRange, bigRange(m))
Next

The number of ranges (files) I need to join together is
variable. Help please.

Posted by Damon Ostrander on November 06, 2001 8:47 AM

Hi Ron,

I believe there are a couple problems here. First, in your code

For m = 1 To NumFiles
Set totalRange = Application.Union(totalRange, bigRange(m))
Next

totalRange is not initialized so I presume its value is Nothing. Unfortunately, Microsoft did not see fit to make the Union method work this generally, and both arguments must point to a range object. You can get around this by writing it:

Set totalRange = bigRange(1)
For m = 2 To NumFiles
Set totalRange = Application.Union(totalRange, bigRange(m))
Next

Secondly, and this is the major problem, you can't define ranges that span multiple worksheets as a single range, 3-D ranges notwithstanding (3-D
ranges can only be used in Excel formulas). Unfortunately, you will have to keep the ranges on different worksheets separate.

Also, just for future reference, you could have used the For..Each loop for your first loop:

For Each W in finalWB.Worksheets
Set bigRange(j) = W.Range(W.Cells(RowOffset + 1, 3), W.Cells(NumRows, 3))
Next W

Happy computing.

Damon