In this example, you have a long list of items in column A starting in cell A1, where many items appear dozens or even hundreds of times in that list.

You want to produce a list of unique items from that list.

To do this manually, the "Unique records only" feature in Advanced Filter is very useful.

Select the column A header, and...
...for Excel versions 2003 and before, from the worksheet menu, click Data > Filter > Advanced Filter.
...for Excel version 2007, from the Data tab, in the Sort & Filter section, click Advanced.

In the Advanced Filter dialog, select the checkbox next to "Unique records only" and click OK.

Then...
...for Excel versions 2003 and before, from the worksheet menu, click Edit > GoTo > Special.
...for Excel version 2007, from the Ribbon click the the Home tab, and in the Editing section, click Find & Select > Go To Special.

In the GoTo Special dialog, select "Visible cells only" and click OK.
Press Ctrl+C to copy the selected visible cells, select a destination worksheet and cell, and press Ctrl+V to paste the unique list of items.
Press the Esc key to exit copy mode.

This macro will produce a unique list in place of the existing list.   It runs quickly because it also uses Advanced Filter for Unique records.

As always, try the macro on a test copy of your workbook first, to make sure it produces the result you want.

Sub DeleteDuplicates()
With Application
.ScreenUpdating = False
Dim LastColumn As Integer
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Offset(0, LastColumn - 1).Value = 1
On Error Resume Next
ActiveSheet.ShowAllData
Columns(LastColumn).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Err.Clear
End With
Columns(LastColumn).Clear
.ScreenUpdating = True
End With
End Sub