看这样改对不: Sub 提取数据() Dim rng, d As Object, d2 As Object, i%, j%, s$, arr1, arr2, t Set d = CreateObject("Scripting.Dictionary") Set d2 = CreateObject("Scripting.Dictionary") rng = Sheets("temp").UsedRange For i = 1 To UBound(rng) 'UBound 函数,返回一个 Long 型数据,其值为指定的数组维可用的最大下标。 For j = 1 To 5 s = s & "," & rng(i, j) Next j If Not d.exists(rng(i, 2)) Then d(rng(i, 2)) = s: s = "" Else d2(rng(i, 2)) = d(rng(i, 2)) & rng(i, 5) d.Remove (rng(i, 2)) s = "" End If Next If d2.Count > 0 Then ReDim arr1(1 To d2.Count, 1 To 16) t = d2.items For i = 1 To d2.Count For j = 1 To 5 arr1(i, j) = Split(t(i - 1), ",")(j) Next j Next i Sheets("重复").[a2].Resize(d2.Count, 5) = arr1 End If If d.Count > 0 Then ReDim arr2(1 To d.Count, 1 To 5) t = d.items For i = 1 To d.Count For j = 1 To 5 arr2(i, j) = Split(t(i - 1), ",")(j) Next j Next i Sheets("不重复").[a1].Resize(d.Count, 5) = arr2 End If MsgBox "提取完毕!" End Sub |