Deleting duplicate entries in an Excel list

T

ticephotos

I work with a list of about 3500 entries in Excel that is updated every 2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?
 
N

Nick

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range you
will get a list of non-duplicate items.

Nick
 
T

ticephotos

I tried the filter and still had almost all of the duplicate entries. How
would I write the code in VB to delete the duplicates?
 
N

Nick

Sub DeleteDups

Dim rInput as range
Dim rNext as range


Set rInput = ActiveCell (Assuming the Cell Selected is the start of your
list and contains the duplicate values)

rInput.Sort key1:=rInput.range

Do until isempty(rinput.value)
set rNext = rInput.Offset(1)
if rInput.Value=rNext.Value then
rInput.Entirerow.Delete
end if
set rInput = rNext
Loop


End Sub

I have tested this actual code put I know this works if I haven't made any
stupid typos

Nick
 
T

ticephotos

Ok, I did something wrong. It says "Arguement not optional" when I run it.
All I did was copy and paste the code minus (Assuming the Cell Selected is
the start of your
 
G

Gord Dibben

tice

Remove the ".range" from rInput.Sort key1:=rInput.range

Sub DeleteDups()
Dim rInput As Range
Dim rNext As Range
Set rInput = ActiveCell
rInput.Sort key1:=rInput.Range
Do Until IsEmpty(rInput.Value)
Set rNext = rInput.Offset(1)
If rInput.Value = rNext.Value Then
rInput.EntireRow.Delete
End If
Set rInput = rNext
Loop
End Sub


Gord Dibben Excel MVP
 
Top