excel - 使用excel VBA从字符串数组中删除重复项

Question

我在 Excel 2010 中有以下 VBA 代码，它将 A 列中的数字读入逗号分隔的字符串。
数字在A列中排序
但是，我想知道是否有一种方法可以在将重复数字读取到逗号分隔变量时删除它们，或者在构建变量后从变量中删除所有重复数字。
这是我构建逗号分隔列表的代码

Dim LR As Long

    Dim RangeOutput
    Dim entry As Variant
    Dim FinalResult As String
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
        On Error Resume Next    'if only 1 row
    
    For Each entry In ThisWorkbook.Sheets("Sheet1").Range("A2:A" & LR)
        If Not IsEmpty(entry.Value) Then
            RangeOutput = RangeOutput & entry.Value & ","
        End If
    Next

    FinalResult = Left(RangeOutput, Len(RangeOutput) - 1)

Answer 1

这就是你可以用字典来做到这一点的方法。

Dim arrData As Variant
Dim dic As Object
Dim idx As Long
    
    arrData = Range("A2").CurrentRegion.Columns(1).Value
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    For idx = 2 To UBound(arrData, 1)
        If arrData(idx, 1) <> "" Then
            dic(arrData(idx, 1)) = ""
        End If
    Next idx

    FinalResult = Join(dic.keys, ",")