|
发表于 2014-5-4 09:22
|
显示全部楼层
本楼为最佳答案
- Sub 提取重复中的不重复记录()
- time1 = Timer
- ar = Sheets("基本信息").Range("B1:D" & Sheets("基本信息").Cells(Rows.Count, 2).End(3).Row)
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
- For i = 2 To UBound(ar)
- xkey = ar(i, 3)
- d(xkey) = d(xkey) + 1 '记录重复次数
- If Not d1.exists(xkey) Then d1(xkey) = i '记录首次出现的行数
- If d(xkey) = 2 Then
- k = k + 1
- For j = 1 To UBound(ar, 2)
- br(k, j) = ar(d1(xkey), j)
- Next
- End If
- Next
- With Sheets("重复")
- .UsedRange.ClearContents
- .Cells(1, 2).Resize(1, UBound(ar, 2)) = Application.Index(ar, 1)
- .Cells(2, 2).Resize(k, UBound(ar, 2)) = br
- End With
- Application.StatusBar = "耗时:" & Format(Timer - time1, "0.00") & "秒" '在状态栏左下角生成耗时时间。
- End Sub
复制代码 |
|