Writing a large VBA array to the worksheet in a single operation, rather than cell by cell, can result in a huge improvement of the speed of many macros. The basic technique was described here. I recently wanted to modify this technique to write a number of arrays from a loop into adjacent ranges on the same sheet, then adjust the range name to cover all the arrays, so they could be cleared in one operation. This is the code I came up with:
' Clear all old data Range("DfileRes").ClearContents ' Reset column offset and maximum number of rows counters, and number of columns per array DatOff = 0 Maxrows = 0 NumCols = 11 For i = 1 to NumOut ' Fill array "Stressa" ' ... ' Get size of output array; Numrows and reset Maxrows if necessary ' ... If Numrows > Maxrows then Maxrows = Numrows ' Write array to spreadsheet Range("DfileRes").Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa ' Recalculate column offset value, "DatOff" DatOff = DatOff + NumCols + 1 ' 1 blank column inserted between each output array Next i ' Resize output range to cover all data Range("DfileRes").Resize(Maxrows, DatOff).Name = "DfileRes"
The line that does all the work is:
- Range(“DfileRes”).Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa
This generates a range offset from the top left cell of the range “DFileRes” by zero rows and DatOff columns, with a size of NumRows x NumCols, and writes the contents of the array “StressA” into that range. Note that a range named “DFileRes” must be created in the spreadsheet for the macro to work.