17

Say if I have a column of numbers (how many of them could vary, but could be anywhere between 1000-10000) and I would like to read all of them into an array in VBA, what is the fastest way of doing so?

Obviously I can create an array of size 10000 and do a for/while-loop but is there any way that's faster than this?

JimmyPena
  • 8,694
  • 6
  • 43
  • 64
AZhu
  • 1,312
  • 6
  • 22
  • 40

1 Answers1

22

Like this?

Dim Ar as Variant

Ar = Sheets("Sheet").Range("A1:A10000").Value

If you do not know the last row then you can find the last row using this and then use the above code as

Ar = Sheets("Sheet").Range("A1:A" & LRow).Value
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • If you're looking for the fastest approach I think you can use `.Value2`. The only difference I have noticed is with dates where `.Value2` returns the serial number instead of the [locally] formatted date. – lori_m Jul 16 '12 at 13:46
  • Value does not return a formatted date, it returns a Variant(datetime) type. If you are seeing it as a formatted date, that is just because the viewer is showing you the default ToString(). It is not a string. – Cor_Blimey Jul 26 '13 at 23:13
  • 10
    Worth mentioning (I didn't see it mentioned elsewhere): this answer returns multidimensional array even though there is only one column involved. The Ar array can be used like Ar(1,1) etc. not like this Ar(1). Also, even if you have Option Base 0 in your module, it will be a Base 1 array. – mountainclimber11 Mar 30 '15 at 20:32
  • Thank you moutainclimber, that was the piece I was missing. – Tom Collins May 29 '15 at 20:10