CODEWITHSUNDEEP
vbaexcelsumifs
user2242044
user2242044

Reputation: 9233

VBA formulas slow with large dataset

I have the following code and causes Excel to crash every time. Column A on Sheet has about 280,000 rows of data. Any thoughts on how to write this code more efficiently? Ideally I'd like to have values there not formulas.

sub test()
    Dim Total_Rows As Long
    Sheets("Sheet1").Activate
    Total_Rows = 13000
    Range("C2", "C" & Total_Rows) = "=SUMIFS('Sheet2'!C:C,'Sheet2'!A:A,'Sheet2!A2)"
End Sub

Upvotes: 0

Views: 660

Answers (2)

MP24
MP24

Reputation: 3200

You could always restrict the number of cells that SUMIFS has to consider, e.g. by using the ranges A1:A400000 instead of A:A.

But I think you should rethink whether it is indeed necessary and makes sense to create 13,000 sumifs formulas.

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

Obvious improvments would be to turn off screen updating and calculation while the macro fills in the formulas, then turn back on at the end. There may be additional ways to improve this but I would start with these low-hanging fruit.

sub test()

    Dim Total_Rows As Long
    Sheets("Sheet1").Activate
    '## Disable screenupdating and calculation
    Application.ScreenUpdating = False
    Application.Calculation = -4135 xlCalculationManual

    Total_Rows = 13000
    Range("C2", "C" & Total_Rows) = "=SUMIFS('Sheet2'!C:C,'Sheet2'!A:A,'Sheet2!A2)"

    '## Put the calculation back to Automatic
    Application.Calculation = -4105 'xlCalculationAutomatic
    ActiveSheet.Calculate  'Force a calculation

    '## Replace the formulas with values only:
    Range("C2", "C" & Total_Rows).Value = Range("C2", "C" & Total_Rows).Value

    '## Allow the screen to update
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions