Code to remove duplicates

[D-Spark]

I have a workbook with a tab called "Data"

On this worksheet are three columns A, B, C....named as "Contract Number", "Post No", "Car Status"

Data on Rows 2 downwards


What I want to acheive is when this worksheet is activated for duplicate rows to be deleted so that Im only left with on permitation occurance of "Contact Number", "Post No", "Car Status"

 

[SteveO59L]

Is your definition of a "duplicate", when all 3 fields are duplicated, or some other permutation ?

 

[D-Spark]

Yes "duplicate" in the context of when collectively Column A, B and C values are duplicated not when one of the values is:


eg

A2=Darrren
B2=hol
C2=jun

A3=Mark
B3=hol
C3=jun

A4=Darren
B4=hol
C4=jun

A5=Mark
B5=hol
C5=jul


row 4 would be deleted as it is a duplicate of row 2

 

[DonkeyOte]

is this a one-off or repetitive task ?

if one off you could simply put a formula in D (array) -- D2 down

=COUNTIF($A$1:$C1,A2:C2)

commit with SHIFT + CTRL + ENTER

any dupes will have a 1 in column D -- you could copy paste values contents of D over the formulae, sort and then remove all 1's

A VBA routine would be option if to be a repetitive task

For VBA purposes can data be sorted ?

 

[Peter_SSs]

Assuming headings in row 1, you could do this fairly easily without a macro as follows:

1. Select the table in columns A:C
2. Data|Filter|Advanced Filter...|Copy to another location|Copy to: D1|Unique records only|OK
3. Delete columns A:C

However, if you do want code, here is the code to do the above (still assuming headings in row 1). Test on a copy of your file.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteDupes()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("A:C")<br>        .AdvancedFilter _<br>            Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>        .Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Wayne01]

Peter

This is a nice simple piece of code but I noticed that it makes all form button objects (used to execute macros on click) progressively move to the left and off the screen. Is there a way to overcome this problem?

Regards

Wayne

 

[Peter_SSs]

Peter

This is a nice simple piece of code but I noticed that it makes all form button objects (used to execute macros on click) progressively move to the left and off the screen. Is there a way to overcome this problem?

Regards

Wayne

Maybe this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteDupes()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("A:C")<br>        .AdvancedFilter _<br>            Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>        .Clear<br>        .Value = .Offset(, 3).Value<br>        .Offset(, 3).Clear<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>