Option Explicit
Sub test()
Dim arr, i, j, m, dic
Set dic = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion.Offset(1).Value
For i = 1 To UBound(arr, 1) - 1
If dic.Exists(arr(i, 1)) Then
If arr(i, 2) > arr(dic(arr(i, 1)), 2) Then arr(dic(arr(i, 1)), 2) = arr(i, 2)
Else
m = m + 1: dic(arr(i, 1)) = m
For j = 1 To UBound(arr, 2)
arr(m, j) = arr(i, j)
Next
End If
Next
With Sheets("sheet2").[a2]
.Resize(UBound(arr, 1), UBound(arr, 2)).ClearContents
.Resize(m, UBound(arr, 2)) = arr
End With
End Sub |