|
- Sub test()
- Dim r%, i%
- Application.ScreenUpdating = False
- t = Timer
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a1:ag" & r)
- End With
- With Worksheets("sheet2")
- .Select
- .Cells.Delete
- .Range("b1").Resize(r, 1) = Application.Index(arr, 0, 1)
- .Range("a1") = "序号"
- .Range("a2").Resize(r - 1, 1) = "=row()-1"
- .Range("a2").Resize(r - 1, 1).Value = .Range("a2").Resize(r - 1, 1).Value
- For j = 5 To 18
- .Cells(1, 3).Resize(r, 1) = Application.Index(arr, 0, j)
- .Range("a1").Resize(r, 3).Sort key1:=Range("b2"), order1:=xlAscending, Key2:=Range("c2"), Order2:=xlDescending, Header:=xlYes
- .Range("d1").Resize(r, 1).ClearContents
- brr = .Range("b1:d" & r)
- For i = 2 To r
- If Len(brr(i, 2)) <> 0 Then
- If brr(i, 1) <> brr(i - 1, 1) Then
- brr(i, 3) = 1
- Else
- If brr(i, 2) = brr(i - 1, 2) Then
- brr(i, 3) = brr(i - 1, 3)
- Else
- brr(i, 3) = brr(i - 1, 3) + 1
- End If
- End If
- End If
- Next
- .Range("d1").Resize(r, 1) = Application.Index(brr, 0, 3)
- .Range("a1").Resize(r, 4).Sort key1:=Range("a2"), order1:=xlAscending, Header:=xlYes
- .Range("d2").Resize(r - 1, 4).Copy Worksheets("sheet1").Cells(2, j + 15)
- Next
- End With
- Application.ScreenUpdating = True
- Worksheets("sheet1").Select
- MsgBox Timer - t
- End Sub
复制代码 |
|