請教諸位大大,
用Excel 的[進階篩選]之[不重覆篩選], 竟然會錯誤, 真奇怪!
(53.3會有兩筆: [D8]及[D12]),
VBA 如下:
Sub 不重覆篩選1()
Dim Ar As Range, eR&
eR = [A65536].End(xlUp).Row
Set Ar = Range("A8:A" & eR)
Ar.AdvancedFilter xlFilterCopy, CopyToRange:=[D8], Unique:=True
End Sub
手動也一樣, 如下圖:
[attach]26913[/attach]
若改用Dictionary則沒問題
Sub 不重覆篩選()
Dim d As Object, Ar As Range, eR&, E
Set d = CreateObject("Scripting.Dictionary")
eR = [A65536].End(xlUp).Row
Set Ar = Range("A8:A" & eR)
For Each E In Ar
d(E.Value) = ""
Next
[D8].Resize(d.Count) = Application.Transpose(d.keys)
End Sub
結果如下圖:
[attach]26914[/attach]
附檔
[attach]26915[/attach]作者: stillfish00 時間: 2017-3-29 17:16
回復 1#yen956
因為 function 假設你的範圍是含標題的，所以把你的A8當成標題列作者: yen956 時間: 2017-3-29 17:56
