CODEWITHSUNDEEP
excelvba
akash bondre
akash bondre

Reputation: 63

Unique:=True Argument in Range.AdvancedFilter Method not working properly

When I use the below code to paste the unique values from column a to b, the first value in column a is always getting repeated at the end of column b.

Sheet1.Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, _
           CopyToRange:=Sheet1.Range("B1:B10"), Unique:=True

column A: test1,test2,test3,test1,test2,test3

column B: test1,test2,test3,test1

As shown above test1 is getting repeated. I tried changing the values, alter sequence in column a but still 1st value is getting repeated.

Upvotes: 0

Views: 1845

Answers (1)

user4039065
user4039065

Reputation:

It would seem that the first test1 is being considered the column header label. When this occurs it is not considered part of the data set. The unique records are being copied across correctly but only as correctly as Excel is determining your range(s) of data.

Excerpt from Range.AdvancedFilter Method:

"First, a header will be included as part of the extract! If the data doesn't have a header, Excel/VBA will kindly add one for you: it takes the first item on the list, duplicates it, and assigns the duplicate as the header. So, if you have Unique:=True, no, you are not going crazy seeing double, and yes, Excel/VBA is working properly (after all, that first value is a Header, not data, and so what's your problem?!?)."

Upvotes: 1

Related Questions