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.
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)