PDA

View Full Version : Solved: Select large array of non-contiguous ranges



nst1107
11-03-2009, 03:48 PM
I have an array of several non-contiguous ranges stored in a variable, and I want to select all of these ranges at once. There are too many ranges to pass their addresses as a string argument to a Range object. I could parse the addresses to several strings and use the Union method, but there's no way to know how many times I will have to split the string, as it can vary in length.

How can I select all the ranges in the array?

nst1107
11-03-2009, 05:46 PM
As an example, see attached workbook.

GTO
11-03-2009, 06:04 PM
Hi Nate,

Maybe I am misunderstanding, but couldn't you loop into Union?

Sub BuildArea()
Dim rngBigRange As Range, r As Range

For Each r In Sheet1.UsedRange
If r.MergeCells Then
If rngBigRange Is Nothing Then
Set rngBigRange = r
Else
Set rngBigRange = Application.Union(rngBigRange, r)
End If
End If
Next
rngBigRange.Interior.ColorIndex = 15
MsgBox rngBigRange.Address
End Sub


Mark

nst1107
11-03-2009, 06:16 PM
Union was the answer. Thanks, Mark! I had considered it, but had not thought about the possibility of using it in a loop like your example.

GTO
11-03-2009, 06:18 PM
:friends: Very happy to help :thumb