CODEWITHSUNDEEP
arraysvbaexcelrange
lisovaccaro
lisovaccaro

Reputation: 33946

Split range containing multiple ranges into an array of ranges?

I'm trying to convert a multiple range selection into an array of ranges.

Right now this is what I'm trying:

Private Function SplitRange(ByRef r As Range) As Range()
    Dim i As Long
    Dim RangesArray() As Range
    Dim AddressArray() As String
    Dim Address As Variant
    i = 0
    AddressArray = Split(r.Address, ",")
    ReDim RangesArray(UBound(AddressArray))
    For Each Address In AddressArray
        Set RangesArray(i) = Range(Address)
        i = i + 1
    Next Address
    ' It works till this point, executing RangesArray(0).Address returns a range address
    SplitRange = RangesArray
    ' Here for some reason neither SplitRange(0).Address or RangesArray(0).Address work
End Function

How can I convert the "compound" range into an array of ranges?

Upvotes: 3

Views: 1520

Answers (1)

mielk
mielk

Reputation: 3940

Each object of Range type has property Areas that contains the collection of its subranges. You can operate on the items from this collection instead of creating array.

But if you really need an array you can easily convert it like that:

Public Function SplitRange(ByRef r As Range) As Range()
    Dim i As Long
    Dim ranges() As Range
    Dim subrange As Range
    '----------------------------------------------------------------

    ReDim ranges(0 To r.Areas.Count - 1)
    For Each subrange In r.Areas
        Set ranges(i) = subrange
        i = i + 1
    Next subrange

    SplitRange = ranges

End Function

Upvotes: 2

Related Questions