VBA Countif taking to Long think maybe Dictionary?

[anthonyexcel]

Good morning,

I have 300,000 rows of data in Column A. I am using a Macro that has countif in it and it takes forever to run. I am looking to have a running count greater than 1. Can anyone offer me a different solution? Thanks in advance!

Sub CountingMoreThanOne()
Dim LR As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
    With Range("B2:B" & LR)
        .Formula = "=IF(COUNTIF($A$2:A2,A2)>1,"""",IF(COUNTIF($A$2:$A$26000,A2)>1,COUNTIF($A$2:$A$26000,A2),""""))"
        .Value = .Value
    End With
End Sub

 

[Fluff]

How about

Sub CountingMoreThanOne()
   Dim Cl As Range
   Dim Itm As Variant
   Dim tmp As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl, 1)
         Else
            tmp = .Item(Cl.Value)
            tmp(1) = tmp(1) + 1
            .Item(Cl.Value) = tmp
         End If
      Next Cl
      For Each Itm In .items
         If Itm(1) > 1 Then Itm(0).Offset(, 1).Value = Itm(1)
      Next Itm
    End With
End Sub

 

[anthonyexcel]

Fluff! Amazing! That is exactly what I am after!! I appreciate all of your help!! Thanks again!

 

[Fluff]

Glad to help & thanks for the feedback