# Remove duplicates in certain order

##### Board Regular
Hello,
I want to remove all duplicates but keep the last one.
I found this code and adapted it to my situation. But I got stuck because the code only compares and removes the next value upwards in order to keep the last duplicate and delete the rest. But I have 3, 4, 5 or more duplicates.
Code:
``````Sub Dupe_Killer_Keep_Last()
Dim lrow As Long

For lrow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(lrow, 2) = Cells(lrow, 2).Offset(-1, 0) Then
Cells(lrow, 2).Offset(-1, 0).Value = ""
End If

Next lrow
End Sub``````

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Code:
``````Sub Dupe_Killer_Keep_Last()
Dim lrow As Long
For lrow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(lrow, 2) = Cells(lrow, 2).Offset(-1, 0) Then
If rng Is Nothing Then
Set rng = Cells(lrow, 2).Offset(-1, 0)
Else
Set rng = Union(rng, Cells(lrow, 2).Offset(-1, 0))
End If
End If
Next lrow
If Not rng Is Nothing Then rng.ClearContents
End Sub``````

Thank you, I am getting a 424 Object Required error. Any ideea why?

Dim Rng As Object
Solved the problem, thank you for your help, it works great.

Code:
``````Sub Dupe_Killer_Keep_Last()
Dim lrow As Long,[COLOR=#ff0000] rng As Range[/COLOR]
For lrow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(lrow, 2) = Cells(lrow, 2).Offset(-1, 0) Then
If rng Is Nothing Then
Set rng = Cells(lrow, 2).Offset(-1, 0)
Else
Set rng = Union(rng, Cells(lrow, 2).Offset(-1, 0))
End If
End If
Next lrow
If Not rng Is Nothing Then rng.ClearContents
End Sub``````

Replies
2
Views
49
Replies
3
Views
100
Replies
5
Views
70
Replies
7
Views
236
Replies
5
Views
229

1,203,487
Messages
6,055,713
Members
444,810
Latest member
ExcelMuch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back