MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing duplicate entries within a column

Posted by Tom on May 04, 2001 6:08 AM

What is the best way to remove duplicate entries within a column automatically.

Posted by Aladin Akyurek on May 04, 2001 6:24 AM


See 16263.html

for a system of formulas that allows for automatic removal of duplicate entries.


Posted by Dave Hawley on May 05, 2001 12:13 AM

Hi Tom

Here is a automated method. This particular code acts on Column A and places a list of all unique entries on a newly created sheet.

Sub CopyUniquesToNewSheet()
'Written by OzGrid Business Applications

'Create a Worksheet
'Extract unique entries only
'Then copy the entire rows to
'the new sheet
Dim RUniqueCells As Range

'Add a new sheet and name it
'If already exists then rename it
On Error Resume Next
Sheets.Add().Name = "Unique Copies"
If ActiveSheet.Name <> "Unique Copies" Then
ActiveSheet.Name = "Unique Copies" & Sheets.Count
End If
On Error GoTo 0

With Sheet1
'Set Range variable to all entries
Set RUniqueCells = Range(.Range("A1"), .Range("A65536").End(xlUp))
'Advance filter to remove duplicates
RUniqueCells.AdvancedFilter _
Action:=xlFilterInPlace, unique:=True
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
End With
Application.CutCopyMode = False
'Release memory
Set RUniqueCells = Nothing
End Sub


OzGrid Business Applications