Speeding up VBA array program 1

[ngerman01]

Right now i have a program that that collect 2 columns of data out of excel and puts them into an array. The way i am doing this is as follows
Range("B1&quot.Select
For I = 0 To 16000
pH(I, 0) = ActiveCell
pH(I, 2) = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
Next I

Now this works fine but it has to run thru all 16 thousand rows. So it takes about 45 seconds. The rest of the program runs thru the array, in VBA, and can do it in about 2 second(if that). I was wondering if there is any way to do something as follows.

var1 = "B1"
var2 = "B2"
For I = 0 To 16000
pH(I, 0) = Range(var1)
pH(I, 2) = Range(var2)
var1 = var1 + 1
var2 = var2 + 2
Next I

Hopefully by doing this it would not have to scroll thru all 16,000 rows.

 

[Zathras]

If you can re-structure your array to map the columns (i.e., columns B and C into ,0 and ,1) then this works practically instantaneously:
[blue]

Sub test()
Dim MyData As Variant
  MyData = Range("B1:C16000")
  MsgBox MyData(15999, 2)
End Sub

[/color]
You don't actually have to work with arrays as such. You can use a variant for an array.

 

[SkipVought]

Hi,

Selecting cells takes ALOT of time...

For I = 0 To 16000
  with cells(I, 2)
    pH(I, 0) = .Value
    pH(I, 2) = .Offset(0, 1).Value
  end with
Next I

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ngerman01]

Thanks to both of you