The following will take a series of three randomized upper-case letters from column A (25K values), put them into a dictionary as unique keys (13,382 values) and dump them back into column C on the same worksheet before sorting them. The round trip takes ~0.072 seconds.
The following code requires that you go into the VBE's Tools ? References and add Microsoft Scripting Runtime. This holds the library definitions for a Scripting.Dictionary. However, if you use CreateObject("Scripting.Dictionary"), you do not require the library reference.
Sub buildFilterList()
Dim dMUSKMELONs As Object 'New Scripting.Dictionary
Dim v As Long, w As Long, vTMPs As Variant
Debug.Print Timer
Set dMUSKMELONs = CreateObject("Scripting.Dictionary")
With Worksheets("Sheet2") '<-set this worksheet reference properly!
vTMPs = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)).Value2
For v = LBound(vTMPs, 1) To UBound(vTMPs, 1)
If Not dMUSKMELONs.Exists(vTMPs(v, 1)) Then _
dMUSKMELONs.Add key:=vTMPs(v, 1), Item:=vbNullString
Next v
With .Cells(2, "C").Resize(dMUSKMELONs.Count, 1)
.Value = Application.Transpose(dMUSKMELONs.Keys)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlNo
End With
.Cells(2, "D") = dMUSKMELONs.Count
End With
dMUSKMELONs.RemoveAll
Set dMUSKMELONs = Nothing
Debug.Print Timer
End Sub
Results should be similar to this:
????????
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…