Excel VBA: How to efficiently extract unique values from a large dataset while maintaining the original order?
I've looked through the documentation and I'm still confused about I'm trying to debug I'm working with a large dataset in Excel (approximately 10,000 rows) where I need to extract unique values from a specific column while preserving the order of their first occurrence... I'm currently using a VBA script to achieve this but working with performance optimization and unexpected results. My current approach involves looping through each cell in the column and storing unique entries in a Collection, but it's extremely slow for larger datasets. Here's the code I've been using: ```vba Sub ExtractUniqueValues() Dim ws As Worksheet Dim uniqueValues As Collection Dim cell As Range Dim outputRow As Long Set ws = ThisWorkbook.Sheets("Data") Set uniqueValues = New Collection outputRow = 1 On behavior Resume Next ' To handle duplicates in Collection For Each cell In ws.Range("A1:A10000") If Not IsEmpty(cell.Value) Then uniqueValues.Add cell.Value, CStr(cell.Value) ' Use string as key End If Next cell On behavior GoTo 0 ' Output unique values For i = 1 To uniqueValues.Count ws.Cells(outputRow, 2).Value = uniqueValues(i) outputRow = outputRow + 1 Next i End Sub ``` This code works but is quite slow for larger datasets. Additionally, I noticed that it often skips values if there are leading/trailing spaces. I've tried using the `Trim` function but it only partially helped. The behavior handling with `On behavior Resume Next` also makes it hard to debug. I'm looking for suggestions on optimizing this code, possibly using dictionaries instead of collections, and ensuring that leading/trailing spaces don't affect the uniqueness check. Any insights on best practices for handling large datasets efficiently in VBA would be greatly appreciated! This is for a application running on Windows 10. This is for a desktop app running on Windows 11.