<3Speed up code? This simple VBA code makes a report but its taking 15 minutes.

Code Tags Added
Your post does not comply with our Forum RULES. Use code tags around code.

Posting code between

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window.

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

If you have a lot of formulas in your worksheets, I would urge you to add code to turn off automatic calculations and turn them back on at the very end of your code.

Try this.

Sub GetBusExpenses()
    Dim x, y(), z, i As Long, ii As Long, lCnt As Long
    
    x = Page1.Cells(1).CurrentRegion
    For i = 2 To Page1.[q2]
        If x(i, 8) <> "" Then
            lCnt = lCnt + 1: ReDim Preserve y(1 To 11, 1 To lCnt)
            y(1, lCnt) = x(i, 8): y(2, lCnt) = x(i, 1): y(3, lCnt) = x(i, 2): y(4, lCnt) = x(i, 4)
            y(5, lCnt) = x(i, 5): y(6, lCnt) = x(i, 3): y(7, lCnt) = x(i, 9): y(8, lCnt) = x(i, 10)
            y(9, lCnt) = x(i, 7): y(10, lCnt) = x(i, 13): y(11, lCnt) = x(i, 14)
        End If
    Next
    ReDim z(1 To lCnt, 1 To 11)
    For i = 1 To lCnt
        For ii = 1 To 11
            z(i, ii) = y(ii, i)
        Next
    Next
    With Page2
        .[a4].Resize(300000, 11).ClearContents
        .[a4].Resize(lCnt, 11) = z
    End With
    
End Sub

In a nutshell, there are 2 ways of writing VBA code, object based and array based.

In the case of object based, as your original code was, for every iteration of the loop data has to be read from the worksheet for the data row concerned, that data then gets written back to the Report sheet in the required order.

In the case of array based, my code, all the data is read in one go and kept in the computer memory in the form of an array (variable x), then the code loops through that array and for every time item x(i, is not empty the required data is placed into a second array (variable y), the size of array y gets increased by 1 for each time x(i, is not empty.

Since it is not possible to increase the first dimension of an array, only the second dimension, array y ends up with the data transposed from what is actually required. It is possible to place the data back to a worksheet with the data in the required format by using "Application.Transpose". However, this method has a tendency to fail with large arrays, so the data is first transposed into a third array (variable z).

All of the above is done within the computer memory using C++ language which is very much faster than VBA, the data is read from the data worksheet just once and placed back to the report sheet just once, as opposed to the thousands of times data gets read and posted back when an object based code is used. This makes the code run much faster (the more data involved the greater the difference between object based and array based run times).

To answer your specific points:

1). This is where the contents of array z are placed onto the Report sheet, Range A4 is first resized to the correct number of rows and columns to match the size of array z.

2). This is where array y gets its second dimension increased by 1 for every iteration of the loop when item x(i, is not empty. The "Preserve" ensures that the contents of the array before re-dimming are not lost.

3). This is where array z is loaded with all the data contained in array y but with the dimensions reversed (data is transposed).

I hope that makes it clearer.

Out of interest how much faster did the code run using my code?

https://www.excelforum.com/exc…15-minutes-to-finish.html