VBA Advanced filter not working

[csilabgirl]

Excel 2002

I have the following code:

Sub RemoveDuplicates()
Range("C2:C100").Copy
Range("D2:D100").PasteSpecial Paste:=xlPasteValues

'Filter out duplicates and copy unique list to "E"

Range("D2", Range("D65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("E2"), Unique:=True
End Sub

The purpose of the code is to remove any duplicate entries and create a new list of unique data. It ALMOST works properly. The only place it does not work is cell D2. If there is another data entry in column D which matches the value in D2, it still shows as duplicated in my final unique list. I have noticed after I run the macro, it renames the range name of cell E2 to "extract". Seems that has something to do with it, but not sure how.

Any ideas on how to amend my code to get rid of this bug would be greatly appreciated!

 

[xenou]

I don't think that can be avoided - Excel assumes you have a column header.
You can code around it after finishing (this is untested as I've just typed it here and not in Excel):

Dim r as Range
If Cells(2,5) = Cells(3,5) Then
    Set r = Range(Cells(3,5),Cells(3,5).End(xlDown))
    r.Copy Destination:=r.Cells(1).Offset(-1)
    r.Cells(r.Cells.Count).ClearContents
    'Application.CutCopyMode = False
End If

 

[csilabgirl]

Xenou,

Thank you for the code. If excel requires a header for this function I could probably add one. If am unable to then I will try your code. Thanks again.