Deleting Duplicate Records

marionmccaleb

New Member
Joined
Sep 26, 2006
Messages
24
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.)
Code:
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

New Member
Joined
Sep 26, 2006
Messages
24
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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

marionmccaleb

New Member
Joined
Sep 26, 2006
Messages
24
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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

New Member
Joined
Jan 24, 2005
Messages
14
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 !
Code:
    '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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

Forum statistics

Threads
1,141,722
Messages
5,708,098
Members
421,546
Latest member
delatollas

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
Top