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.