Remove Duplicate Rows In Excel And Keep Last Duplicate Using VBA With Column Selection Option

[Mohan Kumar]

Dear Sir,

i want to remove all duplicate rows and keep only last duplicate using VBA code with column selection option means whenever i run the VBA code it ask me which column need to be deleted.

i have uploaded an image as an example.

requesting all of you to please provide the solution using VBA.

Thanks.

 

[mse330]

Try the below code

Sub DeleteDups()

Dim Rg As Range
Set Rg = Application.InputBox("Please select the column you need to check for duplicates", Type:=8)

With CreateObject("scripting.dictionary")
    For x = Cells(Rows.Count, Rg.Column).End(xlUp).Row To 1 Step -1
        If Not .exists(Cells(x, Rg.Column).Value) Then
            .Add Cells(x, Rg.Column).Value, Nothing
        Else
            Rows(x).EntireRow.Delete
        End If
    Next
End With

End Sub

 

[Mohan Kumar]

Try the below code

Sub DeleteDups()

Dim Rg As Range
Set Rg = Application.InputBox("Please select the column you need to check for duplicates", Type:=8)

With CreateObject("scripting.dictionary")
    For x = Cells(Rows.Count, Rg.Column).End(xlUp).Row To 1 Step -1
        If Not .exists(Cells(x, Rg.Column).Value) Then
            .Add Cells(x, Rg.Column).Value, Nothing
        Else
            Rows(x).EntireRow.Delete
        End If
    Next
End With

End Sub

Try the below code

Sub DeleteDups()

Dim Rg As Range
Set Rg = Application.InputBox("Please select the column you need to check for duplicates", Type:=8)

With CreateObject("scripting.dictionary")
    For x = Cells(Rows.Count, Rg.Column).End(xlUp).Row To 1 Step -1
        If Not .exists(Cells(x, Rg.Column).Value) Then
            .Add Cells(x, Rg.Column).Value, Nothing
        Else
            Rows(x).EntireRow.Delete
        End If
    Next
End With

End Sub

Thanks A Lot. you are so awesome. thanks once again.

 

[mse330]

You are welcome … Glad to help

 

[duplaly]

You are welcome … Glad to help

Hi Sir
Can you replace the input box by Range (A:B) or columns(1,2)?
Thanks!

 

[mse330]

Hi duplay & welcome to Mr. Excel,

You can't use the same code in two columns simultaneously as it looks for the duplicates in 1 column at a time to work properly. For better responses & aid, I suggest that you start your own new thread with a clear example of what you have & what you need to achieve. You'll more likely get faster responses if you include an example using XL2BB add-in which you can download for free HERE