How to improve loop speed without referencing cells VBA?-CodePudding

I am running the code below to extract data from worksheets and spit it out, it is very slow, any ideas for speed improvements?

Essentially this loop is to extract data from an array (from a database)

I have done the usual turn off calcs etc, what I am looking for is how to loop without going through the worksheet cells as that is what is taking a long time I think.

Thanks!

Here is the code:

    Set RawDataD1WS = PnLWB.Worksheets("Raw_Data_D1")
    Dim VarBunker As Variant
    
    
For i = LBound(VarBunker, 1) To UBound(VarBunker, 1)

    For k = LBound(VarBunker, 1) To UBound(VarBunker, 2)
    BunkerD1WS.Cells(k   2, i   1) = VarBunker(i, k)
    Next k

Next i

CodePudding user response：

Write a 2D Array to a Range

If you're sure the array is 0-based, you can remove the LBounds.
If you're sure the array is 1-based, you can remove the LBound 1s.

Set RawDataD1WS = PnLWB.Worksheets("Raw_Data_D1")
Dim VarBunker As Variant ' redim it and add some values to it

Dim rCount As Long: rCount = UBound(VarBunker, 1) - LBound(VarBunker, 1)   1
Dim cCount As Long: cCount = UBound(VarBunker, 2) - LBound(VarBunker, 2)   1

BunkerD1WS.Range("A2").Resize(rCount, cCount).Value = VarBunker

CodePudding user response：

Do not user array. Arrays have limitations with ranges. You cannot insert 10000 rows to an array. Instead do the following:

Application.ScreenUpdating=false
Application.Calculate=XlCalculation.xlCalculationManual
Sheet1.EnableCalculation=False
' Your code
' ...
' Restore to defaults after processing