Writing array to excel is slow despite only 2500 rows of data.

[Certified]

The following sub procedure is one of a few located in my module.

Sub writetrans()

Sheet3.Activate

    For A = 2 To UBound(MainArray2)
    
        Cells(A, 1) = MainArray2(A, 1)      '<-- Date
        Cells(A, 2) = MainArray2(A, 2)      '<-- Description
        Cells(A, 3) = MainArray2(A, 8)      '<-- Updated Amount
            
                Cells(A, 4) = -MainArray2(A, 9)     '<--Running Balance          
                          
        Cells(A, 5) = MainArray2(A, 10)     '<--Updated Category
        Cells(A, 6) = MainArray2(A, 7)      '<--Accounts
    
    Next A
    
End Sub

The procedure is pretty simple. It writes the elements of an array onto an excel sheet. However, When I run this procedure it is extremely slow. For isntance, I get the " not responding" message whenever I run the procedure. Yet, when I escape from the procedure I can see some rows have been written on the excel sheet. I only have 2500 rows of data it shouldn't be this slow. Also I didn't have this issue when I tested the code a couple of days ago.

What could be causing this issue?

Also I turned off screen updating and auto calculations.

 

[Eric W]

Reading/writing to the sheet is one of the slowest things you can do in VBA. It's far faster to write the entire array in one shot.

In your example, you are rearranging the order of the output columns. What you'd need to do is create an array with the columns in the output order (or just reorder the columns in your existing array). Something like:

    ReDim MainArray3(1 to UBound(MainArray2) - 1, 1 to 6)
    For A = 2 to UBound(MainArray2)
        MainArray3(A - 1, 1) = MainArray2(A, 1)
        MainArray3(A - 1, 2) = MainArray2(A, 2)
        MainArray3(A - 1, 3) = MainArray2(A, 8)
        MainArray3(A - 1, 4) = -MainArray2(A, 9)
        MainArray3(A - 1, 5) = MainArray2(A, 10)
        MainArray3(A - 1, 6) = MainArray2(A, 7)
    Next A

     Sheet3.Cells(2, 1).Resize(UBound(MainArray3), 6).Value = MainArray3

This should run very, very fast. Let us know if this works for you.