|
Sub test()
Dim ar()
i% = [a65536].End(3).Row - 1
Range("C2:C3") = [{1;2}]
Range("C2:C3").AutoFill Destination:=[c2].Resize(i)
Range("a1").Resize(i, 3).Sort [a1], xlDescending, [b1], , xlDescending, , , xlYes
ar = Range("a1").Resize(i, 3).Value
r% = 1
For i = 2 To UBound(ar)
If ar(i, 1) <> bj% Then
k% = 1
bj = ar(i, 1)
Else
k = k + 1
End If
If k <= 40 Then
r = r + 1
ar(r, 1) = ar(i, 1): ar(r, 2) = ar(i, 2): ar(r, 3) = ar(i, 3)
End If
Next
Range("A:C").Clear
[a1].Resize(r, 3) = ar
Range("a1").Resize(r, 3).Sort [c1], , , , , , , xlYes
Range("C:C").Clear
End Sub |
|