Deleting Duplicate Records

[marionmccaleb]

I have a long list of names some of which are duplicated once---I want to delete the records of both of these names (not just one which the Filter function will do). How can I do this?

 

[Anne Troy]

http://support.microsoft.com/?kbid=291320

 

[HalfAce]

Hello marionmccaleb, welcome to the board.
I think you'll find this to be a bit more efficient than the example shown in the MS support
link above.
(Assumes your list is in column A of Sheet1. Change those to suit.)

Option Explicit

Sub DeleteDupesDemo()

Dim LstRw As Long, Rng As Range, c As Range, DltRng As Range

With Sheets("Sheet1")
  LstRw = .Cells(Rows.Count, "A").End(xlUp).Row
  Set Rng = .Range("A1:A" & LstRw)
  For Each c In Rng
    If WorksheetFunction.CountIf(Rng, c.Value) > 1 Then
      If DltRng Is Nothing Then
        Set DltRng = c
      Else
        Set DltRng = Union(DltRng, c)
      End If
    End If
  Next c
  If Not DltRng Is Nothing Then DltRng.EntireRow.Delete
End With

End Sub

 

[marionmccaleb]

Thanks for your suggestion Anne---HalfAce, thanks a bunch for taking the time to develop the code which I will cut and paste into a macro---I will let you know how it works when I find time to check it out---best wishes, Marion McCaleb

 

[HalfAce]

You're most welcome.
By all means, check it out & post back with any changes you'd like to see.

 

[marionmccaleb]

Is there some way to cut and paste the macro you've sent me into the macro panel under TOOLS in Excel or other ways of getting the code in place for a test run?

 

[HalfAce]

Sure. Probably the easiest way is as follows.

To install the code:
1) Save a copy of your real workbook. (Don't ever want to be testing in the real thing) :wink:
2) With your copied workbook open - Press Alt+F11. (This will open up the vb editor.)
3) While in the vb editor, press Ctrl+R. (This will open up the ProjectExplorer if it isn't already.)
4) In the ProjectExplorer pane (usually on the left side of your screen) locate the name of your
copied workbook. Locate & right click on 'ThisWorkbook' below that.
Choose Insert > Module. (This will insert a 'standard' code module named Module1 or
something similar.)
5) Copy the code from here and paste it into the white area on the right that is your new
module.
6) Press Alt+Q to close the vb editor and get back to your worksheet.

To run the code:
1) From the menu, choose Tools > Macro > Macros... (Or you can just press Alt+F8)
2) In the 'Macro' window that opens up, select the macro named 'DeleteDupesDemo' and
click the Run button. (Or you can just double click the macro named 'DeleteDupesDemo'.)
3) Check out sheet1, column A to see that no instances of your duplicated data still exists.

If all is as you want it to be you can install a button on your sheet somewhere, assign
the code to it and use that to run it if you wish.

 

[chearn]

i normally add a column next to my data then put a formula such as
=IF(countif($c$2:c2;c2)>1,"dup","unique") then copy all the way down. Filter the sheet and pull all the "dups" then delete.

 

[erik.van.geit]

i normally add a column next to my data then put a formula such as
=IF(countif($c$2:c2;c2)>1,"dup","unique") then copy all the way down. Filter the sheet and pull all the "dups" then delete.

Hi,

to get some code doing this see
http://www.mrexcel.com/board2/viewtopic.php?t=185393

remove the quote from second line !

    'to remove all duplicates, first appearance included 
    '.FormulaR1C1 = "=IF(COUNTIF(R" & FR & "C[1]:R" & LR & "C[1],RC[1])=1,1,"""")"

the advantage: no loop

kind regards,
Erik

 

[HalfAce]

Yes, chearn suggests another perfectly acceptable (and to a lot of folks preferable) way.
The only real advantage to using the code would be if you have users that aren't all that
savvy with excel or wanted to maybe have it execute automatically whenever a duplicate entry
is made.