?
Solved

Remove Duplicates in Excel 2003

Posted on 2010-09-07
9
Medium Priority
?
1,357 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 1050 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 93

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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 450 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

589 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