|
- Public Sub DoSomethingInActivesheet()
- '作者 DG-NextSeven
- '日期 2019年3月23日
- '说明 处理当前工作表
-
- Dim Wb As Workbook
- Dim Sht As Worksheet
- Dim Rng As Range
- Dim i As Long, j As Long
- Const HEAD_ROW As Long = 1
- Dim Dic As Object
- Set Dic = CreateObject("Scripting.Dictionary")
-
- Set Wb = Application.ThisWorkbook
- Set Sht = Wb.ActiveSheet
- With Sht
- EndRow = .Cells.Find("*", .Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- For i = HEAD_ROW + 1 To EndRow Step 1
- Key = .Cells(i, 3).Value
-
- If Not Dic.Exists(Key) Then
- Dic(Key) = Array(.Cells(i, 1).Value, .Cells(i, 2).Value, .Cells(i, 3).Value, .Cells(i, 4).Value)
- Else
- ar = Dic(Key)
- ar(0) = ar(0) & Chr(10) & .Cells(i, 1).Value
- ar(1) = ar(1) & Chr(10) & .Cells(i, 2).Value
- ar(3) = ar(3) & Chr(10) & .Cells(i, 4).Value
- Dic(Key) = ar
-
- End If
- Next
-
- .Range("a1:D1").Copy .Range("f1")
- Set Rng = .Range("F2")
- For Each OneK In Dic.keys
- Lenth = Dic(OneK)
- Exit For
- Next
- columnsize = UBound(Lenth) + 1
- Set Rng = Rng.Resize(Dic.Count, columnsize)
- Rng.Value = Application.Rept(Dic.Items, 1)
-
- End With
-
- Set Wb = Nothing
- Set Sht = Nothing
- Set Rng = Nothing
- Set Dic = Nothing
-
-
- End Sub
复制代码 |
|