copying unique values into a new column

[keeper85]

Hi,

I need help with the following problem. I have a long column starting in cell AQ3 with a lot of values (where the length of the column and the values might change) and I want to copy the unique values in this column in the column next to it starting with cell AR3. I tried to record a code and slightly modify it using the advanced filter method, however, for some reason it gives me errors (it says that the AdvanceFilter method of range class failed). This is the code I have so far:

Dim TargetPos As Range
Set Target = Range("ar3")
Sheets("Report1").Range("aq3", Range("aq65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=TargetPos, Unique:=True

If someone could provide me with the correction or can suggest an alternative method to Advancedfilter, I would really appreciate it.

 

[VoG]

Shouldn't it be?

Set TargetPos = Range("ar3")

 

[keeper85]

I am sorry: it is TargetPos, I have it that way in my code, but it still does not work

 

[VoG]

This works for me

Dim TargetPos As Range
With Sheets("Report1")
    Set TargetPos = .Range("ar3")
    .Range("aq3", .Range("aq65536").End(xlUp)).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=TargetPos, Unique:=True
End With

 

[keeper85]

I am really surprised it does not work as well. Though I noticed that in certain parts of my sheet the Advanced Filter method does not work, even if I do it manually. Can someone suggest an alternative method to copying unique values into a new column instead of AdvancedFilter, maybe some kind of a loop?