Loading numbers from cells into an array

[ZainE]

Hi. I am trying to copy some data from cells into an array but there's a large amount of data and it takes quite long...

This is what I'm doing:

Sub Test()
Dim myArr(20000, 500) As Double
Dim r, c As Integer
For r = 1 To 20000
    For c = 1 To 500
        myArr(r, c) = Sheets("Sheet2").Cells(r, c).Value
 
        'I also tried the following:
        'myArr(r, c) = Sheets("Sheet2").Range("A1").Offset(r, c).Value
    Next c
Next r
 
End Sub

Is there perhaps a quicker easier way to copy the data into the array?
Thanks

 

[Jonmo1]

Why not just

myArr = Range("A1:Z100")

Adjust range size as needed of coarse...

 

[Weaver]

Define myArr as variant then

myArr=Sheets("Sheet2").range("A1:C2000").Value

or whatever

 

[ZainE]

Define myArr as variant then

myArr=Sheets("Sheet2").range("A1:C2000").Value

or whatever

I tried the following but it still doesn't work...

Sub Test()
Dim myArr(20000, 500) As Variant
myArr = Sheets("Sheet2").Range("A1:SF20000")

End Sub

I get a Compile error: Can't assign to array.

 

[Jonmo1]

Change this
Dim myArr(20000, 500) As Variant
to
Dim myArr As Variant

 

[ZainE]

Thanks!

 

[ZainE]

Change this
Dim myArr(20000, 500) As Variant
to
Dim myArr As Variant

Hi again

Thanks for your help so far - but I get an error (subscript out of range) when I try to reference the array. So if I say myArr(1) I get an error.

How do I use the array?

 

[Jonmo1]

Since it's a 2D array, you would write
Myarr(1,1)

Hope that helps

 

[ZainE]

Sweet!