Delete Duplicates

[dlatha01]

Hi guys

Is there a way to delete duplicates from a spreadsheets, via macros or other routes?

Cheers

David

 

[Colin Legg]

Hi David,

Which version of Excel are you using?

 

[dlatha01]

Hi Colin,

It's Excel 2000

Thanks

David

 

[Stormseed]

If you do a search on this board or even google it, you will find thousands of links which would answer your question.

Edit: you can either use VBA or use an advanced filter to delete duplicates, depends on the layout of your data and your requirement.

 

[Colin Legg]

Hi David,

For VBA, MS Help and Support suggest a couple of procedures on the below link for deleting duplicates:
http://support.microsoft.com/kb/240077


But a lot of people like to use the Advanced Filter to do this:
http://support.microsoft.com/kb/262277


There are also utilities you can download which contain this functionality.


EDIT:
Also see this MrExcel link which gives an overview including some new functionality in Excel 2007:
http://www.mrexcel.com/tip138.shtml

Hope that helps...

 

[Legacy 14611]

Try this macro and see if it's what you want. If you want the duplicates just cleared rather than actively deleted put apostrophes before the 2nd to last and 3rd to last lines

Sub deletedups()
Dim e
With CreateObject("Scripting.Dictionary")
For Each e In ActiveSheet.UsedRange
    If Not .exists(e.Value) Then
        .Add e.Value, Empty
    Else: e.Value = Empty
    End If
Next
End With
Application.ScreenUpdating = 0
ActiveSheet.UsedRange.SpecialCells(4).Delete xlUp
End Sub