Excel VBA Duplication Check on 250,000 rows

[johnbird1988]

Hello,

I am trying to preform a duplicate row check on a data set spanning over 6 columns and approx. 250,000 rows of data.

What I am trying to find is the more efficient way to highlight any duplicate rows. The code I have at the moment currently take around 45 minutes plus and I can’t help think there is a more efficient way.

I have added below an extract of my current code for the duplicate check. In column G I have added a concatenated for A:F. This column is then removed at the end of the code.

For Each w In .Range("G2:G" & iRow)
If Application.CountIf(.Range("G2:G" & iRow), w.value) > 1 Then
w.Interior.Color = RGB(255, 199, 206)
w.Font.Color = RGB(156, 0, 6)
End If
Next w

Any help would be great thank you,

John

 

[Fennek]

Hello,

after reading fora few seconds my immpression is, the code calculates 250,000 ^2 times.

Why not write the "countif" - formula once in the top row and use dopple-click (autofill)) for all 250,000 rows. Then either an autofilter for ">1" or a Range.Find should identify the duplicates.

regards