Hi Lemi,

The code was developed and tested in xl2007. I even deleted all the defined
names and tested to ensure that it would run for you without the need to
define any names first. (The sort part of the code will not run in earlier
versions of xl.)

Using defined names as I have coded this allows the Advanced Filter to work
with different worksheets.

When I posted the amendment did you include the dot in front of Columns
because if not it will produce the error you reported if it deleted the
source data.

Did you make any alterations to the code? If so and it does not work then
post the code with your alterations and I will have a look at it and see if I
can determine what the problem is.

I'll post the code again including the amendment. Try it again.

Sub CreateUniqueList()

'Define name for original data
'Assumes original data in column A
'starting with column header in cell A1.
With Sheets("Sheet1")
.Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "MyData"
End With

'Use another worksheet for unique data
With Sheets("Sheet2")
.Columns("A:A").ClearContents
'Advanced Filter to extract
'unique records
Range("MyData").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), _
Unique:=True

'Define name for unique data
'including column header.
.Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "UniqHeadAndDat"

'Define a name for the data ony
'excluding the column header.
.Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "UniqueDatOnly"

'Clear any existing sort fields.
.Sort.SortFields.Clear

'Set the sort parameters.
.Sort.SortFields _
.Add Key:=.Range("UniqueDatOnly"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

'Sort the data.
With .Sort
.SetRange Range("UniqHeadAndDat")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

UserForm1.ComboBox1.rowsource = "UniqueDatOnly"
End Sub


--
Regards,

OssieMac