I have a weekly task where I need to update a report (currently just over 50K rows) which is growing by around 500 rows every week. After the new data is added manually, I run the code below to do a Sumifs()
to summarize the data.
The data structure is: columns A to C are the criteria columns (numeric-alpha-numeric), column D has the quantity to sum (whole numbers). The data is contiguous. My macro puts the Sumifs()
formula into column E – overwriting what’s there.
My question is: can this task be done quicker? It currently takes me just over a minute to run the macro, but this gets longer as the data grows.
There’s a lot on this site about using Arrays to do tasks more quickly, but none of the examples make much sense to me and I would prefer not to use them if possible.
Sub MySumIfs()
Dim LastRow As Long
LastRow = Sheet1.Range("A1").End(xlDown).Row
With Sheet1.Range("E2:E" & LastRow)
.FormulaR1C1 = "=sumifs(R2C4:R" & LastRow & "C4, R2C1:R" & LastRow & "C1, RC1, R2C2:R" & LastRow & "C2, RC2, R2C3:R" & LastRow & "C3, RC3)"
.Value = .Value
End With
End Sub
See Question&Answers more detail:os