Quickest Way of making an Array

[WhytheQ]

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]

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").Value)

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

[Mike Archer]

If you know that the range will always be A1:A6 and you don't mind having a
long line of code, you can use:
myArray = Array(Range("A1").Value, Range("A2").Value, Range("A3").Value,
Range("A4").Value, Range("A5").Value, Range("A6").Value)

--
Thanks,
Mike


"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.
>
>

[Doug Glancy]

Jason,

Slightly different than Dave's. Apparently, without the Transpose, even a
one-dimensional array is treated as multi-dimensional when created from a
range. The Transpose removes the multi-dimensional quality, so the
reference is to v(i) rather than v(i,1):

Sub test()
Dim v As Variant
Dim i As Long

v = Application.Transpose(ActiveSheet.Range("A1:A6"))
For i = LBound(v) To UBound(v)
Debug.Print "v(" & i & ") = " & v(i)
Next i
End Sub

hth,

Doug


"WhytheQ" <WhytheQ@gmail.com> wrote in message
news:1148139467.490137.95170@j73g2000cwa.googlegroups.com...
> 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.
>

[Don Guillett]

if you don't need commas in between
ma = ActiveSheet.Range("a1:a6").Value

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"WhytheQ" <WhytheQ@gmail.com> wrote in message
news:1148139467.490137.95170@j73g2000cwa.googlegroups.com...
> 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.
>

[Tom Ogilvy]

Dim v as Variant
v = Range("A1:A6")

will put it in a two dimensional base 1 array (regardless of Option Base
setting)

Dim v as Variant
v = Range("A1:A6")
for i = 1 to 6
msgbox "i" & ", " & v(i,1)
Next

---------
This will put it in a 1 D array:

Sub MakeArray()
Dim v As Variant
v = Application.Transpose(Range("A1:A6"))
For i = 1 To 6
MsgBox "i" & ", " & v(i)
Next
End Sub


--
Regards,
Tom Ogilvy


"WhytheQ" <WhytheQ@gmail.com> wrote in message
news:1148139467.490137.95170@j73g2000cwa.googlegroups.com...
> 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.
>

[Alan Beban]

The first array below will be a "vertical" array; the second a
"horizontal" array.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your file, you can
produce a two-dimensional "horizontal" array with

v = ArrayTranspose(Range("A1:A6"))

Alan Beban

Tom Ogilvy wrote:
> Dim v as Variant
> v = Range("A1:A6")
>
> will put it in a two dimensional base 1 array (regardless of Option Base
> setting)
>
> Dim v as Variant
> v = Range("A1:A6")
> for i = 1 to 6
> msgbox "i" & ", " & v(i,1)
> Next
>
> ---------
> This will put it in a 1 D array:
>
> Sub MakeArray()
> Dim v As Variant
> v = Application.Transpose(Range("A1:A6"))
> For i = 1 To 6
> MsgBox "i" & ", " & v(i)
> Next
> End Sub
>
>