Help, inconsistent results on Filter -> Unique, to new location.

[jay]

Hi,

I’m trying to resolve a list of names into a new list of ‘unique’
names.

On sheet_1 I have a list of names in a column, some appear several
times.

I copy this column from sheet_1, to sheet_2 column “A”.

1. I then select -> Data -> Filter -> Advanced Filter -> (Copy to
another location) -> (Unique Records Only).
2. For the “List Range” I select column “A” of sheet_2 which resolves
to “$A:$A”.
3. For the “Copy To:” I select column “B” of sheet_2 which resolve to
“$B:$B”.

Problem:
1. Sometimes the above just does not work, resulting in no copy to:
column “B”, yet sometimes it does (using the exact same data).
2. When I record the process in a macro, it never works ?

Any solutions ?

 

[Ron de Bruin]

Hi

2. When I record the process in a macro, it never works ?

If you run this do you have problems then

Sub test()
Sheets("Sheet2").Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("B1"), Unique:=True
End Sub

 

[Tom Ogilvy]

try using the exact extent of your data with a header in A1 as your data
range. Use a single cell as the destination.

Dim rng as Range
With worksheets("sheet_2")
set rng = .range(.cells(1,1), .cells(rows.count,1).End(xlup))
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("B1"), Unique:=True
End with

 

[Dave Peterson]

Why do you copy column A to sheet2 column A? Can't you just filter uniques
directly to A1 of sheet2 from column A of sheet1?

Manually, insert sheet2
select A1
then do the data|filter|advanced filter
and point at the ranges you want (I'd just use A1 of sheet2 for the destination
range).

I recorded a macro when I did it manually and got this:

Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

I'd be more specific and qualify that copytorange:

Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=sheets("sheet2")Range("A1"), Unique:=True