[原创]VBA筛选非重复值方法比较

yinghaoromeo

方法四，数组循环的算法效率低，我改良之后，方法四就是速度最快的了。

1. 
   
2. Sub test()
   
3. t = Timer
   
4. Application.ScreenUpdating = False
   
5. Dim a(), b(1 To 1000, 1 To 1)
   
6. a = Range("A1:A1000").Value
   
7. k = 1
   
8. n = 0
   
9. For x = 1 To UBound(a)
   
10.     For y = 1 To k
    
11.         If b(k, 1) = a(x, 1) Then
    
12.             n = 0
    
13.             Exit For
    
14.         Else
    
15.             n = 1
    
16.         End If
    
17.     Next
    
18.    
    
19.     If n = 1 Then
    
20.         b(k, 1) = a(x, 1)
    
21.         k = k + 1
    
22.     End If
    
23.     n = 0
    
24. Next
    
25. Range("I1:I1000").Value = b
    
26. t1 = Timer - t
    
27. MsgBox "用时：" & t1 & "秒"
    
28. Application.ScreenUpdating = True
    
29. End Sub
    

复制代码

lxz2393

用字典
Dim d As Object
Dim arr
Application.ScreenUpdating = False
t = Timer
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.Range(Sheet1.[a1], Sheet1.[a65536].End(xlUp))
For i = 1 To UBound(arr)
d(arr(i, 1)) = ""
Next i
Sheet1.[l1].Resize(d.Count, 1) = Application.Transpose(d.keys)
t1 = Timer - t
MsgBox "用时：" & t1 & "秒"
Application.ScreenUpdating = True

dyjtjlt2011

mkpE7gCQ.rar已下载，但晕!

li720608

谢谢楼主分享!

cqr123

好东西，谢谢楼主的分享啊

cqr123

谢谢楼主的分享了，真的不错啊

wozhitianxia666

学习一下，谢谢分享

panan123_0

Sub sql()
Dim sql As String, y, zz, i&
Dim x As Object
t = Timer
Set x = CreateObject("adodb.connection")
    x.Open "provider=microsoft.jet.oledb.4.0;Extended Properties='Excel 8.0;hdr=yes;imex=1';Data Source=" & ThisWorkbook.FullName
sql = "select * from [筛选非重复值$a:a] union select * from [筛选非重复值$a:a]"
Set y = x.Execute(sql)
[s1:s65536].ClearContents
Sheet1.[s1].CopyFromRecordset y
x.Close
    Set x = Nothing
     t1 = Timer - t
MsgBox "用时：" & t1 & "秒"
End Sub

试了下还是字典的快
SQL比不过。

hfreud

总结精辟，有很好的实际参考价值

mesia

收藏了~从看精华帖开始，慢慢学~