Solved: Select large array of non-contiguous ranges

[nst1107]

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]

As an example, see attached workbook.

[GTO]

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]

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]

:friends: Very happy to help :thumb