字典的特性決定了key必須是唯一的。所以可以用字典來(lái)提取唯一值规揪，也就是去除重復(fù)桥氏。
-
單列去重復(fù)
源數(shù)據(jù)
Sub 去重復(fù)()
Dim arr, d As Object, i As Long
arr = Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)'數(shù)組賦值
Set d = CreateObject("scripting.dictionary")'創(chuàng)建字典
For i = 1 To UBound(arr)'遍歷數(shù)組,將數(shù)組元素放進(jìn)字典的key
d(arr(i, 1)) = ""
Next
'd.keys是一維數(shù)組，要放到單元格猛铅，必須轉(zhuǎn)置
[b1].Resize(d.Count, 1) = Application.Transpose(d.keys)
End Sub
-
總之字支，字典里的key是唯一的。
結(jié)果
- 多行求唯一值：
源數(shù)據(jù)
Sub 去重復(fù)()
Dim arr, d As Object, i As Long
arr = Range("a1:b" & Cells(Rows.Count, 1).End(xlUp).Row)
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
d(arr(i, 1)) = arr(i, 2)
Next
[d1].Resize(d.Count, 1) = Application.Transpose(d.keys)
[e1].Resize(d.Count, 1) = Application.Transpose(d.items)
End Sub
Sub 去重復(fù)1()
Dim arr, d As Object, i As Long
arr = Range("a1:b" & Cells(Rows.Count, 1).End(xlUp).Row)
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
s = arr(i, 1) & "/" & arr(i, 2)
d(s) = ""
Next
End Sub
- 假如要求是兩列相同的去重復(fù)奸忽，上面代碼都能夠可以堕伪。其中，Sub 去重復(fù)1()是將兩列通過(guò)符號(hào)[ / ]合并在一起栗菜，形成一個(gè)新的key欠雌，后面再用split函數(shù)分列處理（代碼沒(méi)寫(xiě)）。
- 上面兩種方法都可以用exists來(lái)代替疙筹。
Sub 去重復(fù)2()
Dim arr, d As Object, i As Long, brr()
arr = Range("a1:b" & Cells(Rows.Count, 1).End(xlUp).Row)'將數(shù)據(jù)放進(jìn)數(shù)組arr
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))'定義數(shù)組brr大小
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
s = arr(i, 1) & arr(i, 2)'如果是3列的要求就繼續(xù)使用 & 鏈接....
If Not d.exists(s) Then'如果字典不存在s這個(gè)key
k = k + 1'計(jì)數(shù)
d(s) = k'不存在s這個(gè)key就讓它存在
For j = 1 To UBound(arr, 2)'將數(shù)組arr的值通過(guò)遍歷放到數(shù)組brr
brr(k, j) = arr(i, j)
Next
End If
Next
[d1].Resize(k, UBound(brr, 2)) = brr'輸出唯一值
End Sub
- 用exists的方法富俄，可以直接方便將唯一的數(shù)據(jù)放進(jìn)數(shù)組禁炒，直接輸出。不用轉(zhuǎn)置（效率低）蛙酪，不用管數(shù)據(jù)是要求幾列的唯一值齐苛。
