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

Mohan Kumar

New Member
Joined
Apr 6, 2020
Messages
3
Platform
  1. Windows
  2. Mobile
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.
 

Attachments

  • Image.png
    Image.png
    57.1 KB · Views: 79
Try the below code

VBA 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
 
Upvote 0
Try the below code

VBA 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

VBA 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.
 
Upvote 0
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
 
Upvote 0

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top