|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub tj()
Application.ScreenUpdating = False
Dim ar As Variant
Dim d As Object, dc As Object
Set d = CreateObject("scripting.dictionary")
Set dc = CreateObject("scripting.dictionary")
ReDim arr(1 To Sheets.Count)
With Sheets("Sheet1")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "数据源为空!": End
ar = .Range("a1:b" & r)
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If d(ar(i, 1)) = "" Then
d(ar(i, 1)) = ar(i, 2)
Else
d(ar(i, 1)) = d(ar(i, 1)) & "|" & ar(i, 2)
End If
End If
Next i
Dim br()
ReDim br(1 To d.Count, 1 To 2)
For Each k In d.keys
dc.RemoveAll
rr = Split(d(k), "|")
n = n + 1
br(n, 1) = k
For s = 0 To UBound(rr)
dc(rr(s)) = ""
Next s
br(n, 2) = dc.Count
Next k
[e2:510000] = Empty
.[e2].Resize(n, 2) = br
End With
Application.ScreenUpdating = True
End Sub
|
|