Excel is such a wonderful tool, but if this is not used correctly it could be a pain. If we want to copy/perform some operation on large amount of data and assign values to each cell this could get slow like hell.
if we have below code
Dim Data Data = Sheets("bid").Range(dtarcollet & 8).Resize(numrowstomap, 1) Sheets("worksheet") .Range(dcurcollet & (dcurrownum + 1)).Resize(numrowstomap, 1) = Data
Following will happen inside excel
If you say ‘Sheets(“bid”)’
the sheets object must search for the sheet “bid” every time.
If you say ‘Range(dtarcollet & (ctr + 7))’
the code must calculate the value for “(ctr + 7)” => 8
convert the result to a string => “8”
combine the result with the other string “dtarcollet & (ctr + 7)” => “D8”
the range object must parse the string to get the column and row => D is column 4
prove if column and row is valid and return the value
All this take some time.
Now lets have a look at the magic code :
Sub ArrayTest() Dim R As Range Dim myData Dim i As Long, j As Long myData = Range("A1:C46494") 'OR myData = Range("A1").Resize(46494, 3) 'OR Set R = Range("A1:C46494") myData = R ' 'array lower limit of each dimension is always 1 ' For i = 1 To UBound(myData) ' For j = 1 To UBound(myData, 2) ' 'first dimension is the row, second is the column ' Debug.Print myData(i, j) ' Next ' Next Row Range("K1").Resize(UBound(myData), UBound(myData, 2)) = myData End Sub