CODEWITHSUNDEEP
vbaexcel
walid attia
walid attia

Reputation: 25

How to split range refrence of addresses into elements of one dimenaional array

I have a variable type variant stored in it cells addresses and range addresses

e.g:

Dim cells_addresses as variant
Cells_addresses="$A$1,$C$3:$F$3,$F$4"

Now I want to use a split function or any other sub or function in VBA to split those cells addresses in one dimensional array as the following:

Output_Array="$A$1","$C$3","$D$3","$E$3","$F$3","$F$4"

Anyone has an idea how to split range of addresses into individual elements of an array?

Upvotes: 1

Views: 5087

Answers (3)

Shai Rado
Shai Rado

Reputation: 33692

You can set an Range with the Cells_addresses values you provided. After, loop through the Range.Areas , and nested underneath loop through each Area cells, and each one add to your Output_Array.

Code

Option Explicit

Sub SplitRangeAddrrtoArray()

Dim cells_addresses As Variant
Dim UnionRng As Range
Dim RngArea As Range
Dim C As Range
Dim i As Long
Dim Output_Array() As Variant

cells_addresses = "$A$1,$C$3:$F$3,$F$4"
ReDim Output_Array(0 To 1000) ' init array size >> will optimize later

Set UnionRng = Range(cells_addresses) '<-- Set a Range with the cells addresses provided
' loop through range's areas in case there are multiple areas
For Each RngArea In UnionRng.Areas
    For Each C In RngArea ' loop through the area's cells
        Output_Array(i) = C.Address
        i = i + 1
    Next C
Next RngArea
ReDim Preserve Output_Array(0 To i - 1) '<-- resize to actual populated size

End Sub

Upvotes: 2

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Or like this...

Dim cells_addresses As Variant
Dim OutputArr()
Dim cell As Range
Dim str() As String
Dim i As Long, ii As Long
cells_addresses = "$A$1,$C$3:$F$3,$F$4"
str() = Split(cells_addresses, ",")
For i = 0 To UBound(str)
    For Each cell In Range(str(i))
        ii = ii + 1
        ReDim Preserve OutputArr(1 To ii)
        OutputArr(ii) = cell.Address
    Next cell
Next i
MsgBox Join(OutputArr, ", ")

Upvotes: 0

John Coleman
John Coleman

Reputation: 52008

Here is one way:

Function SplitRanges(RangeString As Variant) As Variant
    Dim ranges As Variant, v As Variant
    Dim c As Range
    Dim i As Long, j As Long, n As Long
    ranges = Split(RangeString, ",")
    For i = 0 To UBound(ranges)
        n = n + Range(ranges(i)).Cells.Count
    Next i
    ReDim v(1 To n)
    i = 0
    For j = 0 To UBound(ranges)
        For Each c In Range(ranges(j)).Cells
            i = i + 1
            v(i) = c.Address
        Next c
    Next j
    SplitRanges = v
End Function

Tested like:

Sub test()
    Dim s As String
    s = "$A$1,$C$3:$F$3,$F$4"
    Debug.Print Join(SplitRanges(s), ",")
End Sub

Output:

$A$1,$C$3,$D$3,$E$3,$F$3,$F$4

Upvotes: 2

Related Questions