Deleting duplicate rows

**Upparna** · 01-31-2012, 05:25 PM

Hi,

I have an excel sheet, that - post data-cleaning - has multiple rows. For example:

A	B	C	D	E	F
Ohio		1020		Paul
Ohio		1020		Paul
New York		1110		Virginia
Ohio		1020		Paul
Ohio		1020		Paul

Which Should ideally look like

A	B	C	D	E	F
Ohio		1020		Paul
New York		1110		Virginia

All those entries for Ohio are duplicates, and I want only one row for Ohio. I have simplified the excel to understand the approach I must take - actual excel sheet has around 15/20 columns for now.

Ideally this would be a function/procedure that I can call from multiple subroutines (at the end of data-cleaning). Tried searching for online examples and other posts in the forum, after my attempts at this fell flat on my face (I even successfully managed to delete *all* rows in the sheet, without any recovery possible, after the cleaning subroutine took 45 mins to run. Brilliant.

Attaching a sample work sheet to illustrate. Is this even possible? Thanks..

**TMShucks** · 01-31-2012, 05:29 PM

As it's an .xlsx file extension, I assume you're using Excel 2007 or 2010.

If so, use Data | remove Duplicates and select columns A, C and E.

Regards, TMS

**JR MacK N Cheez** · 01-31-2012, 05:29 PM

There might be a faster way, but if you hold CTRL you can select more than one row that aren't in order. This will save you from deleting each row 1 by 1.

**Wieli** · 01-31-2012, 05:37 PM

you can just add this code and assaign a button to it

Sub copypasye()
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
    Action:=xlFilterInPlace, Unique:=True

End Sub

**Upparna** · 02-01-2012, 09:00 AM

Thanks all for your responses! I tried all these, and then tried this simple line of code - i think it works like a charm (needs more testing though):

ActiveSheet.Range("A:M").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13), Header:=xlYes

This takes care of the duplicates and retains the header.