Option Explicit
Sub TEST()
Dim ar, br, cr, i&, r&, dic As Object, vKey, iRow&
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
ar = [A1].CurrentRegion
For i = 2 To UBound(ar)
vKey = ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)
dic(vKey) = ""
Next i
iRow = Cells(Rows.Count, "G").End(3).Row
With [G1].Resize(iRow).Resize(, 3)
.Offset(1).Clear
br = .Resize(UBound(ar))
r = 1
End With
For Each vKey In dic.keys
cr = Split(vKey, "|")
r = r + 1
For i = 0 To UBound(cr)
br(r, i + 1) = cr(i)
Next i
Next
With [G1].Resize(r, 3)
.Value = br
ActiveSheet.Sort.SortFields.Clear
.Sort key1:=.Item(2), Order1:=xlAscending, key2:=.Item(3), Order2:=xlAscending, key3:=.Item(1), Order3:=xlAscending, Header:=xlYes
End With
Set dic = Nothing
Application.ScreenUpdating = True
Beep
End Sub
|