Best way to handle duplicate records....

[enggrahul78]

I have records like below. Aim is that the
Last duplicate record shall stay test shall get deleted.
In below example Row 1,Row 2 shall get deleted because A1 is duplicate 3 times. How can I achieve this for large number of Randomly arranged records?



Column A. Column B
A1. B1
A1. B2
A2. A21
A1. B3

[Paul_Hossler]

Maybe

Option Explicit




Sub DeDup()
    Dim r As Range
    Dim i As Long
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    
    With r
        For i = .Rows.Count To 3 Step -1
            Call Range(.Cells(2, 1), .Cells(i - 1, 1)).Replace(.Cells(i, 1).Value, True, xlWhole)
        Next i
        
        On Error Resume Next
        .Columns(1).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        On Error GoTo 0
    End With
End Sub

[p45cal]

Without code you can use Excel's Remove Duplicates but it needs a tweak since that functionality removes duplicates from the bottom up: You could add a column, add numbers to it ascending, then sort the range descending, do the Remove Duplicates, then sort again ascending, finally remove the added column.
Quite a palaver.