-
Solved: Select large array of non-contiguous ranges
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?
-
As an example, see attached workbook.
-
Hi Nate,
Maybe I am misunderstanding, but couldn't you loop into Union?
[vba]
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
[/vba]
Mark
-
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.
-
Very happy to help
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules