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
– smagnan, Commented Aug 6, 2014 at 14:34

Gary's Student · Accepted Answer · 2014-08-06 15:46:53Z

0

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

edited Aug 6, 2014 at 15:46

answered Aug 6, 2014 at 14:36

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sifu Over a year ago

+1 This is good. I would also add an easy to access link to Split() though.

Gary's Student Over a year ago

@Sifu ______________Thanks! _____________I have amended the post.

cbpolley Over a year ago

Thanks for this - Actually the issue is that I am trying to return the values for all cells in a range that is not defined like this, but instead like: Range("A1:A20") Which I would then like to have the cells listed as A1, A2, A3 and so on. Can this be done?

Collectives™ on Stack Overflow

Excel VBA - Split array and return as list

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related