👀 Views: 21 💬 Answers: 1 📅 Created: 2025-06-30

vba excel sorting duplicates VBA

I've spent hours debugging this and I'm integrating two systems and I'm stuck trying to I'm working on a project and hit a roadblock. I'm trying to sort a list of transactions in Excel using VBA, but I want to ensure that only unique combinations of Transaction ID and Date are kept in the sorted result. However, when I execute my code, I receive a 'Sort method failed' behavior. Here's the code I've been working with: ```vba Sub SortUniqueTransactions() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Transactions") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim rng As Range Set rng = ws.Range("A1:C" & lastRow) ' Assuming A has IDs, B has Dates, C has Amounts ' Step 1: Remove duplicates based on Transaction ID and Date rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes ' Step 2: Sort the remaining data by Transaction ID and Date rng.Sort Key1:=ws.Range("A2"), Order1:=xlAscending, Key2:=ws.Range("B2"), Order2:=xlAscending, Header:=xlYes End Sub ``` I have verified that my range includes headers and that there are no empty rows within the data. The RemoveDuplicates method works fine, but as soon as I call the Sort method, I run into the behavior. I'm using Excel 2016, and my data doesn't contain any blank cells in the key columns. Can anyone guide to troubleshoot this scenario or suggest a better approach to achieve unique sorting? I have also tried explicitly defining the range for the sort keys but still get the same behavior. This is happening in both development and production on Ubuntu 20.04. Am I approaching this the right way? What am I doing wrong?