by

Fast copy an Array (or text files) directly to a range of cells in Excel, VBA macro code.

In Excel it is much faster to write large sets of data to a spreadsheet all at once instead of using a loop, by copying an array variable to a range of cells directly. This can speed up an Excel macro significantly, especially in large complex workbooks.

It’s especially handy if you want to read text files, line by line, and copy the text from the text files directly to a sheet – I give an example of that at the bottom of this post. The “Transpose” function is indispensable here. Here is some sample code showing 1D and 2D arrays being copied directly to ranges of cells:

Sub test()

Dim finaldata() As Integer

ReDim finaldata(4) 'This array now has 5 columns, numbered 0 to 4.

finaldata(0) = 111
finaldata(1) = 222
finaldata(2) = 333
finaldata(3) = 444
finaldata(4) = 555

'This line writes data to the right, since a 1D array is like a series of columns, with 1 row:
Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 5)).value = finaldata

'This line writes data downwards because of the Transpose function:
Sheets("Sheet1").Range(Cells(1, 1), Cells(5, 1)).value = Application.WorksheetFunction.Transpose(finaldata)

End Sub

We can do a similar thing with 2D arrays:

Sub test()
Dim i As Integer
Dim finaldata() As Integer

ReDim finaldata(4, 2) '5 rows, 3 columns in array (0->4 rows, 0->2 columns)

For i = 0 To 4
    finaldata(i, 0) = CStr(i) & 0
    finaldata(i, 1) = CStr(i) & 1
    finaldata(i, 2) = CStr(i) & 2
Next i

'this line writes the data with rows and columns the same as the array:
Sheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)).value = finaldata

'this line writes the data transposed, flipping rows and columns
Sheets("Sheet1").Range(Cells(1, 1), Cells(3, 5)).value = Application.WorksheetFunction.Transpose(finaldata)

End Sub

Here is an example of reading a text file line by line into an array, and then fast copying that array directly to a sheet:


Dim resultfilearray() As String
Dim i as Integer

Set MyFile = fso.OpenTextFile(fpath, ForReading)  'fpath is a String, path to the file.
i = 0
Do While MyFile.AtEndOfStream <> True
     ReDim Preserve resultfilearray(i)  'result file array is the array that will contain each line from the text file.
     resultfilearray(i) = MyFile.ReadLine
     i = i + 1
Loop
MyFile.Close
'completed loading the entire text file into the array.

'write the array of strings to the sheet, in first column, downwards:
Range(Cells(1, 1), Cells(i, 1)).value = Application.WorksheetFunction.Transpose(finaldata)