Tom Black

asked on

# Remove Duplicates in Excel 2003

What is the simpliest VBA code to remove duplicate items within a column or columns in excel 2003?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

If you want vba code:

If the column is Column A and you want duplicates in column A to be deleted (the entire row)

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

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

ASKER

This is not running either

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

ASKER

The link lead me in the right direcdtion but still had to figure out the code based on that suggestion

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2123-Eliminating-duplicate-data-with-Duplicate-Master-V2.html