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

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").AdvancedFilte r
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/lib...ffice.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 he
http://www.mytempdir.com/1244482

Thanks for any help!
=)

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

Oh, and Microsoft Excel 2003 SP2 used.

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

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?

wrote:
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").AdvancedFilte r
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/lib...ffice.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 he
http://www.mytempdir.com/1244482

Thanks for any help!
=)



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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

On Mar 6, 4:26 pm, Debra Dalgleish wrote:
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....

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

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

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

On Mar 6, 5:40 pm, "merjet" wrote:
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.


So how do I adjust the line:
Worksheets("NAMES").Range("A2:A500").AdvancedFilte r
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!

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

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

wrote:
On Mar 6, 5:40 pm, "merjet" wrote:

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.



So how do I adjust the line:
Worksheets("NAMES").Range("A2:A500").AdvancedFilte r
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
Contextures
http://www.contextures.com/tiptech.html

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

So how do I adjust the line:
Worksheets("NAMES").Range("A2:A500").AdvancedFilte r
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

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

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.... :]