Writing Data to cells

[Bob]

Hi everyone:

In excel 2003 VBA, I have a rather large 2D array. After doing some sorting
and other things, I am trying to write the data back to the spreadsheet
using loops. However, this writing take a bit too long, and is noticeable.
All my work takes 35 msec, whereas writing it takes 923 msec. Is there a
quick and fast way of dumping an array onto the spreadsheet? Thanks for
your help, and here is my code for writing the data.

'N is the number or rows
'M is the number of columns
c = M + 1
For i = 1 To N
For j = 1 To M
Cells(i, j + c + 1) = AOrig(ind(i), j)
Next j
Next i

Bob

 

[JMB]

Yes - if you have a 2x2 array you could say
Range("A1:B2").Value = YourArray

Or if the array is dynamic you could use something similar to the code
below, although you'd have to make the necessary adjustments if the lower
bounds of your array are something other than 1.


Sub test()
Dim arrTemp(1 To 2, 1 To 2) As Long

arrTemp(1, 1) = 5
arrTemp(1, 2) = 10
arrTemp(2, 1) = 15
arrTemp(2, 2) = 20

Range("A1").Resize(UBound(arrTemp, 2), _
UBound(arrTemp, 1)).Value = arrTemp

End Sub

 

[Bob]

Thank you.

Yes - if you have a 2x2 array you could say
Range("A1:B2").Value = YourArray

Or if the array is dynamic you could use something similar to the code
below, although you'd have to make the necessary adjustments if the lower
bounds of your array are something other than 1.


Sub test()
Dim arrTemp(1 To 2, 1 To 2) As Long

arrTemp(1, 1) = 5
arrTemp(1, 2) = 10
arrTemp(2, 1) = 15
arrTemp(2, 2) = 20

Range("A1").Resize(UBound(arrTemp, 2), _
UBound(arrTemp, 1)).Value = arrTemp

End Sub