Excel VBA - Split array and return as list

Question

I have a range of cell addresses saved as a range, which I then want to return as a list of individual cell addresses. I can't seem to find anything online that says this is posible, does anyone know otherwise?

Many thanks

What I have got so far:

Function AN()

Dim CellRange As String Dim a() As String Dim intCount As Integer

Dim strTemp

For Each C In ActiveCell.Offset(0, 0).Range("A1:A46")
    If C = "" Then
        C.Select
        CellRange = Range("C" & ActiveCell.Row, ActiveCell.Offset(0, -1)).Address
        a = split(CellRange)
            For intCount = LBound(a) To UBound(a)
            MsgBox a(intCount)

            Next

This gives me the two extremes of the range, but not the cells in between. Split doesn't seem to be working - I'm still getting to grips with it so please forgive any errors there .. it's been a long day!

Actually I think you can take each cell in the range and add then to a list ... what have you tried? — smagnan

Gary's Student Gary's Student · Accepted Answer · 2014-08-06T14:36:33

Here is one way to convert a range into a string of addresses, comma-separated:

Sub dural()
    Dim r As Range, s As String
    Set r = Range("A1,B9,D13,J78,l987")
    s = r.Address(0, 0)
    MsgBox s
End Sub

You can make this into an array of strings using Split():

Sub dural()
    Dim r As Range, s As String
    Set r = Range("A1,B9,D13,J78,l987")
    s = r.Address(0, 0)
    MsgBox s
    ary = Split(s, ",")
End Sub

Excel VBA - Split array and return as list

1 Answers