You can pick up a range directly from a range in a worksheet with something
like:

Option Explicit
Sub testme()

Dim myArr As Variant
Dim iCtr As Long
myArr = ActiveSheet.Range("a1:a6").Value

For iCtr = LBound(myArr, 1) To UBound(myArr, 1)
MsgBox myArr(iCtr, 1)
Next iCtr

End Sub

But notice that the array is actually 2 dimensions--rows by columns. In this
case, it's a 6 x 1 array.

If you want to make it a single dimension array, you could use
application.transpose() to pick it up. Be aware that in xl2k and below (IIRC),
application.transpose is limited to 5461 elements. xl2002+ can use the whole
column.

Sub testme2()

Dim myArr As Variant
Dim iCtr As Long
myArr = Application.Transpose(ActiveSheet.Range("a1:a6").V alue)

For iCtr = LBound(myArr) To UBound(myArr)
MsgBox myArr(iCtr)
Next iCtr

End Sub

WhytheQ wrote:

1
2
3
4
5
6

What is the quickest way of putting the above into an array?
The above numbers are located on sheet1 range("A1:A6")

I use what I believe is the normal way of filling the array i.e a For
Next Loop - but maybe there are better ways of going about it?

Regards,
Jason.

--

Dave Peterson