Duplicates in a list

[Airbiskit]

Trying to delete lines from a list of Account names, I have tried using the link below

240077 - Macro Examples to Delete Duplicate Items in a List

http://support.microsoft.com/default.aspx?scid=kb;en-us...

but I get this error.

Runtime Error 9
Subscript out of range.

 

[KenWright]

Just select your list of names, do Data / Filter / Advanced Filter / Check 'Copy To Another Location' and 'Unique Values' and then select where you want the list to go.

This will give you a list with no duplicates.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[gizmo1973]

Didn't know thatn one Ken!!
Here's my contribution

faq68-5446

Regards, Phil.

We're Coming To Get You!!!!

"Where there's blame there's DPlank"

 

[Airbiskit]

Ken, Used your suggestion first and this worked a treat, many thanks.

Phil, thanks for your suggestion, I will have a play with this.

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[gizmo1973]

Don't play too hard (oh well it is Firday!!)

Regards, Phil.

We're Coming To Get You!!!!

"Where there's blame there's DPlank"

 

[storym]

I have to delete multiple numbers on a daily basis when I have to combine two files with over 30,000 entries in them. I created this sub and placed it in my personal.xls and then assigned a shortcut to it (CTRL+SHIFT+D) so that it was available for use whenever I needed it. I also added a few options that can be used if I don't want to delete the duplicate but still need to know when a duplicate exists.

Sub deleteDuplicate()
'
' deleteDuplicate Macro
' Macro recorded 5/6/2003 by StoryM
'

'

For i = 1 To 65536
firstUPC = ActiveCell.Text
If firstUPC = "" Then Exit Sub
ActiveCell.Offset(1, 0).Select
secondUPC = ActiveCell.Text
If firstUPC = secondUPC Then

' Use this code to stop the macro when a duplicate is found.
' Exit Sub

' Use this code to highlight duplicates.
' With Selection.Interior
' .ColorIndex = 6
' .Pattern = xlSolid
' End With

' Use this code to delete duplicates.
Selection.EntireRow.Delete

ActiveCell.Offset(-1, 0).Select
i = i - 1
End If
Next i

End Sub