CODEWITHSUNDEEP
arraysexcelvba
user3466555
user3466555

Reputation: 25

Direct way to put a column in an array in VBA

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!

Upvotes: 1

Views: 376

Answers (3)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 0

Santosh
Santosh

Reputation: 12353

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

Upvotes: 1

Related Questions