"VBA Study" How to assign cell data to an array

Iamlaosong

assigning data in a worksheet to an array or assigning an array's data to a worksheet generally has two types, one is the loop method, the other is a pass-through, and this method is commonly used for situations where each data is specially handled, and the other is to use assignment statements at a time, and the second method is much faster for speed. Look at the following routines:

sub tt ()     Dim arr1 (240000, 4)     Dim arr2 ()       & nbsp Lineno = [A1048576]. End (Xlup). Row       ' number of rows     ' loops array assignment, array Myarr must first define size     T1 = Now ()     for i = 3 to lineno& nbsp        k = i-2        ARR1 (k, 1) = Cells (i, 1)         ARR1 (k, 2) =  Cells (I, 2)         ARR1 (k, 3) = Cells (i, 3)         ARR1 (k, 4) = Cells (I, 4)     Next i    t2 = Now ()     Cells (2, 5) = TimeValue (T2)-TimeValue (T1)     ' Once array assignment, array a RR cannot define size and type     T1 = Now ()     ARR2 = Range ("a3:d" & Lineno)     t2 = Now ()     Ce LLS (2, 6) = TimeValue (T2)-TimeValue (T1)     MsgBox arr1 (20000, 2) & "=" & Arr2 (20000, 2) End Sub 

However, it is important to note that the method array of the loop assignment must first define the dimension and size before it can be used, but the one-time assignment is exactly the opposite, the dimension and size cannot be defined, otherwise an error will be given.

The test found that, even with 20多万条 data, the first method took very little (4.62963E-05), while the second method was almost free of time (0).

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"VBA Study" How to assign cell data to an array