CODEWITHSUNDEEP
vbaexcelcountifcumulative-sum
Eric Jang
Eric Jang

Reputation: 33

Excel VBA: Is there a faster way to do a running total?

I'm using a countif function with an expanding range and auto-filling to get the running totals and was wondering if there was a better way to go about it.

COUNTIF($A$2:A2,A2) + autofill down

There seems to be no problem if there's just a few thousand rows, but when it gets to 10k+ rows it slows down considerably.

I searched online for some clues, but all the "faster" ways had to do with the TOTAL count, not a RUNNING count.

Thank you for your help!

Upvotes: 1

Views: 1364

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You can use a Dictionary, faster on large data because it uses an efficient search algorithm (binary search). On large datasets this should make a noticeable difference.

Sub RunningCounts()
  Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
  Dim r As Range: Set r = Sheet1.Range("A2", Sheet1.Cells(Sheet1.Rows.count, "A").End(xlUp))
  Dim values: values = r.Value2
  Dim i As Long
  For i = LBound(values, 1) To UBound(values, 1)
     dict(values(i, 1)) = dict(values(i, 1)) + 1
     values(i, 1) = dict(values(i, 1))
  Next
  r.Offset(, 1).value = values ' <-- writes results on next column. change as needed
End Sub

Upvotes: 1

Related Questions