VBA formulas slow with large dataset

0
votes

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
2
vbaexcelsumifs
Does that formula work in cell C2? It looks circular, assuming it is going on sheet2. - Dave.Gugg
It is happening on a different sheet. Updated code to be more direct. - user2242044
@simoco I know that is the right approach but this is a one time thing and I don't want to have to learn about database connections just for this. If I type that formula in manually and drag down it works (but takes about a minute). - user2242044

2 Answers

2
votes

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.

1
votes

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