VBA: How to identify and manage duplicates in a multi-column Excel range without slowing down performance?
I've tried everything I can think of but I'm currently working with a large Excel sheet (over 10,000 rows) where I need to identify and handle duplicates across multiple columns (specifically columns A, B, and C). I've tried using the built-in `Remove Duplicates` feature, but it's not flexible enough for my needs, and I want to flag duplicates instead of removing them. Performance is a concern because I need this to run efficiently without freezing Excel. Here's what I've tried so far: ```vba Sub FlagDuplicates() Dim ws As Worksheet Dim lastRow As Long Dim dict As Object Dim cell As Range Dim key As String Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set dict = CreateObject("Scripting.Dictionary") For Each cell In ws.Range("A2:C" & lastRow) If Not IsEmpty(cell) Then key = Join(Application.Transpose(Application.Transpose(cell.Resize(1, 3).Value)), "|") If dict.Exists(key) Then ws.Cells(cell.Row, 4).Value = "Duplicate" Else dict.Add key, Nothing End If End If Next cell End Sub ``` While this works, I’ve noticed that the performance significantly decreases as the size of the data increases. Sometimes I get a runtime error suggesting that the dictionary can’t handle the number of items being added. I'm curious if there are more efficient ways to scan for duplicates, especially if I could do this without using a dictionary. Any suggestions on best practices for handling this scenario or optimizing the performance would be greatly appreciated. Additionally, if there are alternative methods to efficiently compare multiple columns, I'd love to hear those as well. Thanks in advance! Any ideas what could be causing this?