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)