VBA: AdvancedFilter - Unique Copy of column of names doesn't work?!

[chiendh]

Hi!

I'm using this Visual Basic code to copy a column of names into
another column. This other column will only contain the unique names
from the original column of names.

Worksheets("NAMES").Range("A2:A500").AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Worksheets("NAMES").Range("C2"),
Unique:=True

For whatever reason, it doesn't work a certain list of names I'm
testing, and I'm stuck trying to figure out how to get it to work (or
create another formula to extract the unique names). Doesn't even work
correctly if I paste the names below into a new spreadsheet and only
the function above!

Microsoft's VBA help seems pretty clear cut on this - simply have
Unique=True:
http://msdn2.microsoft.com/en-us/library/aa221800(office.11).aspx

Alcantara-Hernandez
Alkire
Alcantara-Hernandez
Alkire
Alcantara-Hernandez
Alcantara-Hernandez
Anderson
Bauer
Alcantara-Hernandez
Alcantara-Hernandez
Alcantara-Hernandez
Alkire
Alkire
Baker
Zolfagari
Alcantara-Hernandez
Alcantara-Hernandez
Alkire
Anderson
Asrani

I've put a copy of the spreadsheet here:
http://www.mytempdir.com/1244482

Thanks for any help!
=)

 

[chiendh]

Oh, and Microsoft Excel 2003 SP2 used.

 

[Debra Dalgleish]

If you don't have a heading on the column, the first value will be
treated as the heading, and may appear twice in the list of unique
values. Could that be the problem?

 

[chiendh]

If you don't have a heading on the column, the first value will be
treated as the heading, and may appear twice in the list of unique
values. Could that be the problem?

Nope. I've got a NAMES header on the column in the actual (huge)
spreadsheet I'm writing. It's some sort of odd bug/behaviour in
Excel....

 

[merjet]

Nope. I've got a NAMES header on the column in the actual (huge)

spreadsheet I'm writing. It's some sort of odd bug/behaviour in
Excel....

Debra's intuition was correct. The Names header isn't being included
in the range to filter, so Excel takes the first instance of Alcantara-
Hernandez to be a header.

Merjet

 

[chiendh]

Debra's intuition was correct. The Names header isn't being included
in the range to filter, so Excel takes the first instance of Alcantara-
Hernandez to be a header.

So how do I adjust the line:
Worksheets("NAMES").Range("A2:A500").AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Worksheets("NAMES").Range("C2"),
Unique:=True

to reflect this? (assume that the data is in A2 through A500, the
header at A1)

If I used ...Range ("A1:A500")... it doesn't even do this correctly -
accidentally copies the header into the list.
If I leave it as is, it duplicates Alcantara-Hernandez.

Is there some other trick for putting the column header besides it's
current row 1 position in the column of names?

Thanks!

 

[Debra Dalgleish]

No, the filtered list will always include the heading cell.
You could delete or clear that cell in your code.

 

[merjet]

So how do I adjust the line:

Worksheets("NAMES").Range("A2:A500").AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Worksheets("NAMES").Range("C2"),
Unique:=True

Change: Range("A2:A500") and Range("C2")
To: Range("A1:A500") and Range("C1")

But first clear Range(C1). After sorting you can change its content
to "Unique Names" if desired.

Merjet

 

[chiendh]

Change: Range("A2:A500") and Range("C2")

To: Range("A1:A500") and Range("C1")

But first clear Range(C1). After sorting you can change its content
to "Unique Names" if desired.

Thanks!!!! That worked perfectly! =)

Tip was to make sure C1 was clear, too!

...can't believe Microsoft is still leaving out details like this
in their documentation.....

Thanks to everyone for their replies!
Back to coding.... :]