Many delete-duplicate for...next loops are very slow, especially when you have thousands of names to loop through several thousand times for each name on the list.
This function that I made is, in my opinion, the best and quickest way to do it without too many annoying and slow for loops (good for lists 1k +). It compares lstA to lstB. Anything that is in both lists is added to lstC. To change it so that anything that isn't in both lists is added to lstC, change the "If Not" to "If".
You'll need this API declaration:
Code:
Private Declare Function SendMessageString Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
Const LB_FINDSTRINGEXACT = &H1A2
And here's the function I put together:
Code:
Function CompareLists(FirstList As ListBox, SecondList As ListBox, FinalList As ListBox)
Dim i As Integer
For i = FirstList.ListCount - 1 To 0 Step -1
If Not SendMessageString(FirstList.hwnd, LB_FINDSTRINGEXACT, -1, ByVal SecondList.List(i)) <> i Then
FinalList.AddItem FirstList.List(i)
End If
Next i
End Function
It's called with the code (example). This belongs in a sub or another seperate function:
Code:
CompareLists lstA, lstB, lstC
If you only have a single list:
Change the function so it only uses a single list in all instances. It that case, the function will remove duplicates and leaves it with a single instance of the duplicate. You'd have to change the code within the for loop to "lst.RemoveItem i". And then you're set.
Hope this helps.
