How to remove duplicate rows from specific column using macro vba

Question

I have a problem on how to remove rows with the same values for specific Columns in excel.

Please see screenshot below for my problem:

In row 4: FullName: A SeqNo:003
In row 5: FullName: A SeqNo:003

So row 4 and 5 has the same value so I want to remove one of them but I don't want to remove row automatically by random but in condition that only the latest date will remain.

Condition be like this:

This should be the correct output:

So how can I achieve this by macro vba?

Thanks in advance!

Answer 1

Try this.

Sub test()
    Dim vDB, vR()
    Dim X As New Collection
    Dim Str As String
    Dim i As Long, j As Long, r As Long, c As Integer
    Dim n As Long

    vDB = Range("a1").CurrentRegion
    r = UBound(vDB, 1)
    c = UBound(vDB, 2)

    On Error Resume Next

    For i = 1 To r
        Str = vDB(i, 1) & vDB(i, 4)
        Err.Clear
        X.Add Str, Str
        If Err.Number = 0 Then
            n = n + 1
            ReDim Preserve vR(1 To c, 1 To n)
            For j = 1 To c
                vR(j, n) = vDB(i, j)
            Next j
        End If
    Next i
    For i = 1 To n
        For j = 1 To r
            If vDB(j, 1) = vR(1, i) And vDB(j, 4) = vR(4, i) Then
                If vDB(j, 5) >= vR(5, i) Then
                    vR(2, i) = vDB(j, 2)
                    vR(5, i) = vDB(j, 5)
                End If
            End If
        Next j
    Next i
    Sheets.Add
    Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)

End Sub