Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! =) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: AdvancedFilter - Unique Copy of column of names doesn't work?!
Oh, and Microsoft Excel 2003 SP2 used.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... :] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding unique names--then converting those names to unique number | Excel Discussion (Misc queries) | |||
Advancedfilter doesn't work when Excel opens, but does when run macro | Excel Programming | |||
Selection to Array then unique with different fields/columns like advancedfilter | Excel Programming | |||
Selection to Array then unique with different fields/columns like advancedfilter | Excel Programming | |||
Stutter in AdvancedFilter Unique Results? | Excel Programming |