How to VBA code to count duplicates FAST

< Back to Search results

 Category: [Excel]  Demo Available 

How to VBA code to count duplicates FAST




The user has a macro that loops through thousands of cells and creates a long list of item numbers (see attached file, column A).

Once the list is created (as a preparation for the next steps in the code), the user needs to count how many times each item appears. To be more specific, the user needs to know the sequential number of the appearance (#1, #2, #3, etc.) (See attached file, column B).

The best way the user has found to achieve that, is by using the countif function, which the code embeds in the file (later on, the code copy-paste the entire column as values).

The thing is, these formulas take a lot of time to calculate.

My questions is: Is there another way (perhaps an array formula) to get the “counting” added next to each item number (replacing this countif formula), which will work much faster?






This uses a dictionary

Sub countThings()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim items As Object
    Application.ScreenUpdating = False
    Set ws = Sheet1
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    Set items = CreateObject("Scripting.Dictionary")
    For x = 1 To lastRow
        If Not items.exists(ws.Range("A" & x).Value) Then
            items.Add ws.Range("A" & x).Value, 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
            items(ws.Range("A" & x).Value) = items(ws.Range("A" & x).Value) + 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
        End If
    Next x
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by bryce.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to remove duplicates from dropdown list combobox
How to create a macro to move duplicates
How to copy a sheet and rename from a list, ignore duplicates
How to use IndexMatch formula that ignores duplicates


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.





stars (0 Reviews)