CODEWITHSUNDEEP
vbaexcelexcel-2010excel-2007
davinceleecode
davinceleecode

Reputation: 807

How to remove duplicate rows from specific column using macro vba

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:
enter image description here

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: enter image description here



This should be the correct output: enter image description here


So how can I achieve this by macro vba?

Thanks in advance!

Upvotes: 1

Views: 543

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 1

Absinthe
Absinthe

Reputation: 3391

Record a macro of you sorting by the date column newest to oldest, then using the remove duplicates feature on the three columns but untick the date column in the remove duplicates dialogue. By default Excel keeps the first duplicate it comes to so by sorting you will remove the rows with older dates.

You should be able to tidy up the recorded code yourself.

Upvotes: 1

Related Questions