Solved

Remove Duplicates in Excel 2003

Posted on 2010-09-07
9
1,203 Views
Last Modified: 2012-05-10
What is the simpliest VBA code to remove duplicate items within a column or columns in excel 2003?
0
Comment
Question by:bearblack
9 Comments
 
LVL 8

Accepted Solution

by:
sjl1986 earned 350 total points
ID: 33620456
http://support.microsoft.com/kb/262277

This might help you. It won't do an ongoing removal though if the cells get duplicated later.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33620487
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620515
If you want vba code:
If the column is Column A and you want duplicates in column A to be deleted (the entire row)
 

dim c1 as range

dim c2 as range

for each c in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row

     for each c in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row

             if c1.value = c2.value then

                   c2.entirerow.delete

             end if

      next

next

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620531
oops, need to make one edit.....don't run the abiove code......run this one
dim c1 as range  

dim c2 as range  

for each c in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row  

     for each c in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row  

             if c1.value = c2.value and c1.row <> c2.row then  

                   c2.entirerow.delete  

             end if  

      next  

next

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:bearblack
ID: 33620832
This is not running either
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620882
oops:
dim c1 as range    

dim c2 as range    

for each c1 in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row    

     for each c2 in activesheet.range("A1:A" & activesheet.range("A" & activesheet.range("A:A").rows.count).end(xlup).row    

             if c1.value = c2.value and c1.row <> c2.row then    

                   c2.entirerow.delete    

             end if    

      next    

next

Open in new window

0
 
LVL 13

Assisted Solution

by:MWGainesJR
MWGainesJR earned 150 total points
ID: 33620891
make sure you put it in a sub.....
0
 
LVL 2

Author Comment

by:bearblack
ID: 33621469
This seems to work
    Range("A2").Select

    Selection.AutoFilter

    Range(Selection, Selection.End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _

        "B2"), Unique:=True

    Range("A:A").Delete

Open in new window

0
 
LVL 2

Author Closing Comment

by:bearblack
ID: 33621544
The link lead me in the right direcdtion but still had to figure out the code based on that suggestion
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now