How do I create an array of the contents of a excel column

Question

I have 10 values in column A of a excel spreadsheet (it will be more) is there a way to take the values of the column and put them into an array?

And if possible would it be possible to put the values in a different order than they are in in the spreadsheet. For example, if my spreadsheet values are "Apple" "Orange" and "Banana", then I would like my array to look something like, position 0 "Orange" position 1 "Banana" and position 2 "Apple".

Does anybody know how this might be done? By the way, it needs to be scalable from 10 to 1000 values without editing the code much

What do you need to achieve exactly ? Is an array really what you need or you just need that data ordered (which is best achieved without an array). Finally, what have you tried yet ? — ApplePie, Sep 09 '12 at 20:18
If you want to sort your data, the best way is to let Excel sort it for you. *Then* you can read the data into anarray, ifyou wish. — Treb, Sep 09 '12 at 20:23
I need to be able to access and modify the data through vb code, as far as I know that is how it is done — Devon M, Sep 10 '12 at 00:21
My ultimate goal is to build a kind of deal or no deal type game, the player starts with a bunch of buttons, each hiding a value, when the user clicks it the value of the button is displayed and the button disappears. — Devon M, Sep 10 '12 at 00:23
@geekman2 if any of the answers solved your problem, you can mark that answer as accepted by clicking the tick mark under the up vote/down voted arrows. See this [link](http://meta.stackexchange.com/a/5235/182513) for more details. — psubsee2003, Oct 01 '12 at 07:57

score 1 · Accepted Answer · edited May 08 '18 at 21:48

You can create an indexed array for a single column without looping as follows

Sub GetArray()
Dim X
Dim lngCol As Long
lngCol = Cells(Rows.Count, "A").End(xlUp).Row
X = Application.Transpose(Application.Evaluate("If(row(A1:A" & lngCol & "),row(1:" & lngCol & ")-1 & A1:a" & lngCol & ",0)"))
End Sub

You didn't post how you wanted to sort the data? enter image description here Udpated for random ordering

Sub GetArray2()
Dim X()
Dim lngCol As Long
lngCol = Cells(Rows.Count, "A").End(xlUp).Row
X = Application.Transpose(Range("A1:A" & lngCol))
Call ShuffleArrayInPlace(X())
End Sub

The next sub uses a modified version of Chip Pearson's ShuffleArray

Sub ShuffleArrayInPlace(InArray() As Variant)
'http://www.cpearson.com/excel/ShuffleArray.aspx
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArrayInPlace
' This shuffles InArray to random order, randomized in place.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long

    Randomize
    For N = LBound(InArray) To UBound(InArray)
        J = CLng(((UBound(InArray) - N) * Rnd) + N)
        If N <> J Then
            Temp = InArray(N)
            InArray(N) = InArray(J)
            InArray(J) = Temp
        End If
    Next N
      For N = LBound(InArray) To UBound(InArray)
      InArray(N) = N - 1 & " " & InArray(N)
      Debug.Print InArray(N)
      Next N
End Sub

enter image description here

I want it more or less randomly sorted – Devon M Sep 10 '12 at 16:56 — Devon M, Sep 10 '12 at 16:56

enderland · Answer 2 · 2012-09-10T01:12:49.267

0

A way to read in an entire range into an array:

Sub readText()

    Dim i As Integer
    Dim dataStr As String
    Dim arr As Variant

    'read data
    arr = Range("A1:A10").Value

    'display the data...
    For i = 1 To UBound(arr)
        'add the value at this point - given by arr(i,1) - to the string. You can access these elements 
        'directly via this sort of array notation
        dataStr = dataStr & arr(i, 1) & vbCrLf

    Next i
    'show what was in those cells
    MsgBox (dataStr)
    MsgBox (arr(3,1) )
End Sub

It is almost assuredly easier to sort in Excel first (ie alphabetical? increasing? by order? etc) rather than doing so in vba.

edited Sep 10 '12 at 01:12

answered Sep 09 '12 at 22:04

enderland

13,825
17
98
152

Thanks, but that displays the entire list, I need to be able to access each piece individually. Maybe there is a gap in my knowledge, if so please enlighten me, I'm pretty new to VBA and this is my first experience with arrays – Devon M Sep 10 '12 at 00:30

How do I create an array of the contents of a excel column

2 Answers2