CODEWITHSUNDEEP
vbaexcel
aidobs
aidobs

Reputation: 1

Can I speed up this VBA code?

I've put together a simple macro for sifting through column A and transposing every 3 rows into one row... (i.e. A1,A2,A3 go to A1,B1,C1 , A4,A5,A6 go to A2,B2,C2 etc... )

it works really well, however, I'll be pushing the scope to the max, i.e trying to go to rows.count

I was wondering if anyone has an insight on on how to speed up the code, it takes about 40 seconds to get through 200,000 lines, and sort of bombs out (91 error) sometime after that ....

any ideas on some improvements?

here's the code :

Sub arrsampWORKS1()
    Dim array_example(3)
    Dim Destination As Range
    Dim p As Double

    'StartTime = Timer

    For q = 0 To 40
        p = q * 3
        'Storing values in the array
        For i = 0 To 2
            array_example(i) = Range("A" & i + 1 + p)
        Next

        Set Destination = Range(Cells(q + 1, 4), Cells(q + 1, 7))
        Set Destination = Destination.Resize(1, 3)
        Destination.Value = array_example
    Next
    'MsgBox Timer - StartTime & " seconds"
End Sub

Upvotes: 0

Views: 100

Answers (2)

Cohan
Cohan

Reputation: 4544

In general, if I have a macro that I want to be more efficient, I turn off screen updating and auto calculation at the beginning of the macro. By default, every time there is a change, excel will update every formula in the open workbooks.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

At the end of the macro, I re-enable them

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

This cuts the time dramatically, but is only useful if you do not need to recalculate everything.

If you need to calculate at a given time in the macro, you can use

Application.Calculate 'calculate everything
wksht.Calculate 'calculate a specified worksheet

If you still need better performance, then start reworking your code.

Upvotes: 1

George
George

Reputation: 1489

Writing to the spreadsheet and setting ranges take time to initialize and generally you can save time by storing more items in memory and then write at the end.

I would change your code to have 2 loops - first to read all of the data and create a temp variable that holds the info, then a 2nd loop that outputs all of the data.

Sub revised()

Dim array_example(3) As Variant, alldata() As Variant
Dim Destination As Range, Data As Range
Dim p As Double, iCount As Double, iArraysCount As Double
Dim step As Integer

'StartTime = Timer
Set Data = Range("A1")
ReDim Preserve alldata(0)
iArraysCount = 0
step = 3

    For iCount = 1 To 45 Step step
            'Storing values in the array
            For i = 0 To 2
                array_example(i) = Data.Cells(iCount + i)
            Next
            ReDim Preserve alldata(iArraysCount)
            alldata(iArraysCount) = array_example
            iArraysCount = iArraysCount + 1
    Next

Set Destination = Range("B1")
For iCount = 0 To UBound(alldata)
        Destination.Cells(iCount + 1, 1).Value = alldata(iCount)(0)
        Destination.Cells(iCount + 1, 2).Value = alldata(iCount)(1)
        Destination.Cells(iCount + 1, 3).Value = alldata(iCount)(2)
Next
'MsgBox Timer - StartTime & " seconds"

End Sub

Upvotes: 0

Related Questions