|
完整代码
Sub test()
Set dic = CreateObject("scripting.dictionary")
Dim arr, brr()
arr = Sheets("Sheet1").[a1].CurrentRegion
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
ReDim Preserve arr(1 To UBound(arr), 1 To UBound(arr, 2) + 1)
Application.DisplayAlerts = False
For Each sht In Sheets
If sht.Name <> "Sheet1" Then sht.Delete
Next sht
For i = 2 To UBound(arr)
关键字 = arr(i, 1)
If Not dic.exists(关键字) Then
dic(关键字) = 1
arr(i, UBound(arr, 2)) = dic(关键字)
Else
dic(关键字) = dic(关键字) + 1
arr(i, UBound(arr, 2)) = dic(关键字)
End If
Next
dic.RemoveAll
For i = 1 To UBound(arr)
dic(arr(i, UBound(arr, 2))) = ""
Next
For Each v In dic.keys
If v > 0 Then
l = 0
For i = 1 To UBound(arr)
If arr(i, UBound(arr, 2)) = v Then
l = l + 1
For j = 1 To UBound(arr, 2) - 1
brr(l, j) = arr(i, j)
Next
End If
Next
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "订单" & v
ActiveSheet.[a1:c1] = Array("名称", "单位", "数量")
ActiveSheet.[a2].Resize(l, UBound(brr, 2)) = brr
End If
Next
Sheets("Sheet1").Activate
Application.DisplayAlerts = True
End Sub |
|