如果数据量真的很大,这个还能快一点点:
- Sub test()
- Dim arr, r&, br(), cr(), i&, j&, n&, c&, p&
- tt = Timer
- arr = Range("a1").CurrentRegion.Resize(, 6)
- r = UBound(arr)
- ReDim br(1 To r, 1 To 1), cr(1 To r, 1 To 1)
- br(1, 1) = 0: p = 1: n = br(p, 1)
- For i = 2 To r
- For j = 6 To 1 Step -1
- If arr(i, j) <> arr(p, j) Then Exit For
- Next
- If j > 0 Then
- n = n + 1
- cr(n, 1) = n
- p = i
- End If
- br(i, 1) = n
- Next
- c = Sheet1.Rows.Find("*", , xlValues, , 2, 2).Column + 1
- Application.ScreenUpdating = False
- Cells(1, c).Resize(r) = br
- Cells(r + 1, c).Resize(n) = cr
- With Range("a1").Resize(r + n, c)
- .Sort .Cells(1, c), xlAscending, Header:=xlYes
- End With
- Columns(c).Clear
- Application.ScreenUpdating = True
- MsgBox Format(Timer - tt, "用时0.00秒")
- End Sub
复制代码 |