|
Sub 分类汇总()
Application.ScreenUpdating = False
Dim ar As Variant
Dim arr()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("原工作表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "原工作表为空!": End
ar = .Range("a1:x" & r)
End With
ReDim arr(1 To UBound(ar), 1 To 5)
For i = 2 To UBound(ar)
If VBA.Trim(ar(i, 12)) <> "" Then
t = d(VBA.Trim(ar(i, 12)))
If t = "" Then
k = k + 1
d(VBA.Trim(ar(i, 12))) = k
t = k
arr(k, 1) = ar(i, 12)
arr(k, 5) = ar(i, 18)
End If
arr(t, 2) = arr(t, 2) + ar(i, 13)
arr(t, 3) = arr(t, 3) + ar(i, 14)
arr(t, 4) = arr(t, 4) + ar(i, 17)
End If
Next i
With Sheets("去重合并后数据")
.UsedRange.Offset(2) = Empty
.[a3].Resize(k, UBound(arr, 2)) = arr
End With
Set d = Nothing
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|