Solved

Remove Duplicates in Excel 2003

Posted on 2010-09-07
9
1,211 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

860 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