Automatically Delete Duplicate Cell Rows which have a unique

[nischal4487]

Hi,

I frequently receive a dump of excel with huge date. However, in column A I have unique values (eg:- Employee id) which are duplicated several times which I have delete manually. everytime.
Could you please help me to give a query or formula or any guidance to automatically have rows which have the duplicates only one Id remain?

Advance thanks for your support

 

[Kerryx]

Dont know what version of excel you have but under the data option in nwere versions there is an option to delete Duplicate rows

 

[nischal4487]

Dont know what version of excel you have but under the data option in nwere versions there is an option to delete Duplicate rows
View attachment 64462

Thanks for the response. However this is a manual effort for me. But if I can automate it and have the duplicate rows removed, it would more time consuming and helpful

 

[Kerryx]

You can use it to record a Macro

Sub dup2()
'
' dup2 Macro
'
    ActiveSheet.Range("$A$1:$E$5").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Amend range as required, Columns number 1 + column A to use a s reference for finding and deleting your duplicate rows use whichever column has ID in it, assign button to macro, voila

 

[nischal4487]

Sub dup2()
'
' dup2 Macro
'
ActiveSheet.Range("$A$1:$E$5").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Amend range as required, Columns number 1 + column A to use a s reference for finding and deleting your duplicate rows use whichever column has ID in it, assign button to macro

Hi Kerry,

Thank you so much for your reply. I know I am sounding dunce , but can you please let know where do I input the code you have provided.

Thanks Again.

 

[Kerryx]

Ok since it is your first time please be aware running VBA code there is no go back option if you make a mistake hence it is always best to run on a copy of your workbook / sheet when testing

Open your workbook, Press Alt+F11
This will open your VBA editor screen

Select the Sheet
past the code

Then run the code

there are plenty of walkthroughs more detailed than this on the web.

 

[nischal4487]

Thanks so much Kerry this worked, Is there any code that can have entire duplicated rows deleted rather just making the duplicated cells blank.Kindly help

 

[Kerryx]

replace the code with this, i am assuming the ID is in column A and that once you find a duplicate you want it deleted

Sub RemoveDuplicateRows()

Dim Rng As Range
Dim x As Long
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
x = Rng.Rows.Count

For x = x To 1 Step -1
    With Rng.Cells(x, 1)
       If WorksheetFunction.CountIf(Rng, .Value) > 1 Then
                  .EntireRow.Delete
       End If
     End With
Next x

End Sub