Code to remove duplicates

D-Spark

Board Regular
Joined
Sep 27, 2007
Messages
182
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"
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Is your definition of a "duplicate", when all 3 fields are duplicated, or some other permutation ?
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top