|
本帖最后由 chart888 于 2017-5-12 11:24 编辑
- Private Sub CommandButton1_Click()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim ar
- Dim r, i, r1, r2 As Integer
- r = ThisWorkbook.Worksheets("1").Range("C65536").End(xlUp).Row
- Set d = CreateObject("Scripting.Dictionary")
- ar = ThisWorkbook.Worksheets("1").Range("C4:D" & r)
- For i = 1 To UBound(ar)
- If ar(i, 1) <> "" Then d(ar(i, 1)) = d(ar(i, 1)) + ar(i, 2)
- Next
-
- With ThisWorkbook.Worksheets("2")
- .Range("B2:C65536").ClearContents
- .Range("F2:G65536").ClearContents
- .Range("J2:K65536").ClearContents
- .Range("B2").Resize(d.Count, 2) = Application.Transpose(Array(d.keys, d.items))
- If .Range("B18").Value <> "" Then
- r1 = .Range("B65536").End(xlUp).Row
- .Range("B18").Resize(r1 - 17, 2).Copy
- .Range("F2").PasteSpecial Paste:=xlPasteValues
- .Range("B18:C65536").ClearContents
- End If
- If .Range("F18").Value <> "" Then
- r2 = .Range("F65536").End(xlUp).Row
- .Range("F18").Resize(r12 - 17, 2).Copy
- .Range("J2").PasteSpecial Paste:=xlPasteValues
- .Range("F18:G65536").ClearContents
- End If
- End With
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
-
- End Sub
复制代码 |
|