VBA to Copy Unique Rows from One Sheet to Another Based on Multiple Criteria - Issues with Array Filtering
I'm prototyping a solution and I'm working through a tutorial and I'm working on a personal project and Quick question that's been bugging me - I'm trying to copy unique rows from one worksheet to another based on multiple criteria, but I'm running into issues with filtering the data correctly. My goal is to identify unique entries where both Column A (Customer ID) and Column B (Order Date) match specific criteria. However, my current approach using a nested loop is proving inefficient and resulting in duplicate entries in the destination sheet. Here's the code snippet I've been using: ```vba Sub CopyUniqueRows() Dim sourceSheet As Worksheet Dim destSheet As Worksheet Dim lastRow As Long Dim destRow As Long Dim i As Long Dim customerID As String Dim orderDate As Date Dim uniqueDict As Object Set uniqueDict = CreateObject("Scripting.Dictionary") Set sourceSheet = ThisWorkbook.Sheets("Source") Set destSheet = ThisWorkbook.Sheets("Destination") lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row destRow = 1 For i = 2 To lastRow customerID = sourceSheet.Cells(i, 1).Value orderDate = sourceSheet.Cells(i, 2).Value If Not uniqueDict.exists(customerID & "|" & orderDate) Then uniqueDict.Add customerID & "|" & orderDate, Nothing destSheet.Cells(destRow, 1).Value = customerID destSheet.Cells(destRow, 2).Value = orderDate destRow = destRow + 1 End If Next i End Sub ``` This implementation uses a `Dictionary` to track unique combinations of Customer ID and Order Date, but it still results in duplicates when the source data has multiple entries with the same ID and date. I've confirmed that the keys in `uniqueDict` should be unique, yet the destination sheet occasionally gets duplicates. I’ve also tried using a `Collection` instead of a `Dictionary`, but I received a "Type Mismatch" error when trying to add keys in that context. What could I be missing in my logic, or is there a better approach to ensure I’m copying truly unique rows? Any advice on optimizing this process would be greatly appreciated! Thanks in advance! Any suggestions would be helpful. Could this be a known issue?