大神给推荐个去重公式，不要最简，只要效率最高，处理速度最

vba字典

先透视生成结果，然后用函数转换到其他固定的表格中，这样就不会卡。
不要一步到位，可以把透视作为中间表。

Sub 去重复()
Dim arr, d, m
arr = ActiveSheet.UsedRange
Set d = CreateObject("scripting.dictionary")
For Each m In arr
If m <> "" Then d(m) = ""
Next m
If d.Count > 0 Then
Worksheets.Add
[a1].Resize(d.Count, 1) = Application.Transpose(d.keys)
MsgBox "去重复成功！"
End If
End Sub

把代码放在worksheet的range change事件中就行

Public d As Object
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim arr, m
arr = ActiveSheet.UsedRange
If d Is Nothing Then Set d = CreateObject("scripting.dictionary")
If d.Count > 0 Then d.RemoveAll
If ActiveSheet.UsedRange.Count < 2 Then sets: Exit Sub
For Each m In arr
If m <> "" Then d(m) = ""
Next m
If d.Count > 0 Then
'Worksheets.Add
ActiveSheet.UsedRange = ""
[a1].Resize(d.Count, 1) = Application.Transpose(d.Keys)
'MsgBox "去重复成功！"
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub sets()
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

字典不适合处理大数据，例如字典词条超过5万条以上的，据说会很卡