1

I want to put the values of a column in an array or list, without having to do a for loop.

So basically if I have:

Col A
---------
212
411
432

I want to put those values in an array or list. This is how I am thinking of doing it, but I don't know the VBA syntax:

  1. reference Col A somehow (i.e. myColumn = column("Col A")
  2. insert the column values into the array(i.e. myArray = myColumn

I appreciate your help!

Community
  • 1
  • 1
user3466555
  • 25
  • 3
  • Why don't you want to use a for loop? – rory.ap Apr 05 '14 at 14:09
  • Because I thought that there could be a more direct way to do this that could me more efficient. – user3466555 Apr 05 '14 at 14:15
  • I wouldn't worry about efficiency unless you're running this over and over again against thousands of rows of data. The for loop is the way to go. There is no way I know about to get the data into an array without a loop, and even if there were, it would be looping under the hood anyway -- you just wouldn't know about it. – rory.ap Apr 05 '14 at 14:16
  • I will be running this for a lot of data, that is why I am concerned. – user3466555 Apr 05 '14 at 14:19
  • 2
    `arr = Range("A1:A3")` this statement puts the values of range in array without looping. See my answer below. – Santosh Apr 05 '14 at 14:19
  • @Santosh -- Well I'll be damned. So it does! – rory.ap Apr 05 '14 at 14:21

3 Answers3

1

Try this

Sub Main()
 Dim arr() As Variant
 arr = Range("A1:A3")


For i = LBound(arr) To UBound(arr)
    Debug.Print arr(i, 1)
Next

End Sub
Santosh
  • 12,175
  • 4
  • 41
  • 72
  • Hi Santosh, thanks for that I think it works. Two more questions though. How can I see the print statement and if I want to index the array, would it just be arr(1)? – user3466555 Apr 05 '14 at 14:33
  • @user3466555 For loop does show how to print values of array. See this [link](http://www.cpearson.com/excel/ArraysAndRanges.aspx) may be helpful. – Santosh Apr 05 '14 at 14:39
  • 1
    Note that `Dim arr() As Variant` declares an array of `Variants`. Since a `Variant` can hold an array, that's unnecessary. Instead, use `Dim arr As Variant` and leave the rest of your code the same. – Doug Glancy Apr 05 '14 at 15:21
1

Also when working wth a single column or row, you can use TRANSPOSE to create a 1D variant array, as opposed to the default 2D array (as Santosh did).

This 1D method is useful in combination with the Filter, Join and Split functions, such as Is it possible to fill an array with row numbers which match a certain criteria without looping?

Sub OneD()
Dim arr
arr = Application.Transpose(Range("A1:A3").Value2)
End Sub

or

Sub TwoD()
Dim arr
arr = Range("A1:A3").Value2
End Sub
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
0

Here is one way:

Sub dural()
    Dim myColumn As Range
    Dim myArray() as Variant
    Set myColumn = Range("A1:A3")
    myArray = myColumn
    For X = 1 To 3
        MsgBox myArray(X, 1)
    Next X
End Sub

There are ways to make the size of both the range and array variable.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99