Delete rows that contain duplicate data in one column

**Knawl** · 06-10-2009, 05:34 AM

I am trying to delete rows (with macro) that contain duplicate data in a specific column.

So I may have:

John Smith 01/01/1990 Ref:111
Jane Smith 02/02/1990 Ref:123
John Smith 00/00/0000 Ref:111

If the ref is the same then the whole row should be deleted. The rest of the row will not be the same. So the result would be:

John Smith 01/01/1990 Ref:111
Jane Smith 02/02/1990 Ref:123

Thanks.

**JBeaucaire** · 06-10-2009, 02:24 PM

So, when we're deleting, we're deleting the secondary ones, keeping the first. Also, can you tell me what column those ref values are in?

**JBeaucaire** · 06-10-2009, 02:27 PM

Here's a stock macro I have for this, I've put the REF in column C, if that's wrong, change that reference before running the macro:

Please Login or Register  to view this content.

**WeatherGuy** · 06-10-2009, 10:58 PM

Originally Posted by JBeaucaire

Here's a stock macro I have for this, I've put the REF in column C, if that's wrong, change that reference before running the macro:

Please Login or Register  to view this content.

Should the code LastRow = Range("A"... be LastRow = Range("C"... ? Also, can this be easily modified to NOT leave a row that contained one of the duplicate values? So the only rows remaining after the macro is run are the rows that contained unique values initially in column C. Thanks.

**Knawl** · 06-11-2009, 04:06 AM

Excellent thank you.

**JBeaucaire** · 06-11-2009, 08:57 AM

You can solve for LR in any column and then use it in any range, but in this simplest of application it probably SHOULD be initially solved in column C, too. Only the OP (or you in your own sheet) really know the best way to solve then use the LastRow variable.

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)