Solved

Remove Duplicates in Excel 2003

Posted on 2010-09-07
9
1,198 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

12 Experts available now in Live!

Get 1:1 Help Now