Solved

Remove Duplicates in Excel 2003

Posted on 2010-09-07
9
1,254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

626 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