CODEWITHSUNDEEP
excelvba
user7857211
user7857211

Reputation:

Why does Range().AdvancedFilter for unique values ignore the first row?

TLDR: Why does the sorting algorithm ignore (in regards of uniqueness) and just copy the first value?

The following code

Sub test()

    ActiveSheet.Range("A1:A7").AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B1"), _
    Unique:=True

End Sub

filters the following rows:

A1 to A7:
1
1
2
3
4
1
2

to:

1
1
2
3
4

I tried it with text, different starting rows, sorting in place instead of copying to another range, but it seems as if the first value is just copied and not compared with anything in regards to being unique.

Range.AdvancedFilter method (Excel) does not mention anything.

The result should be that the Range A1:A7 is uniquely filtered (sort order is not important):

1
2
3
4

Upvotes: 1

Views: 279

Answers (2)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2791

To answer the question directly, as I just spent time debugging this myself. You MUST have a "Header" row in your range that is being filtered. This is not considered a bug, but a requirement of AdvancedFilter.

I have no sources for this other than this page, but I can confirm OP's results and comments make sense and support this answer. If anybody has official links/docs regarding this matter please share!

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

You can use RemoveDuplicates on Column B:

ActiveSheet.Range("A1:A7").Copy ActiveSheet.Range("B1")
ActiveSheet.Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo

Of course, there's no need to use ActiveSheet, and it's better to use fully qualified Worksheets instead:

With Worksheets("YourSheetName")
    .Range("A1:A7").Copy .Range("B1")
    .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
End With

Upvotes: -1

Related Questions