VBA: AdvancedFilter to get unique values after AutoFilter

Question

I'm trying to get the unique names in column B after filtering column A and then pasting them in column C as following:

Range("A1:B1").Select
            Selection.AutoFilter Field:=1, Criteria1:="=" + Type

Range("B1").Select
ActiveSheet.Range("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("C1"), Unique:=True

However this will bypass the filtering of column A and will return all unique names of column B.

How can I use Autofilter or AdvancedFilter with multiple criterias?

Thanks,

Answer 1

you can go like follows

Range("C1").Value = Range("B1").Value '<~~ heading of the column to get unique values from
Range("D1:D2") = Application.Transpose(Array(Range("a1").Value, myType)) '<~~ filtering criteria (temporarily used)

Range("A:A").SpecialCells(xlCellTypeConstants).Resize(, 2).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("D1:D2"), CopyToRange:=ActiveSheet.Range("C1"), Unique:=True

Range("D1:D2").ClearContents  '<~~ clear filtering criteria temporarily used cells

as you see the actual job is done in one line only, which is preceded by two "setting" lines (column "C" heading and filtering criteria temporary cells) and followed by one line for the deletion of temporary cells.

if you can't write in Range("D1:D2") you can use any other range (provided it's a 1 column-2 rows one) and change code accordingly.

---

or you can go with the "copying" approach

With Range("A:A").SpecialCells(xlCellTypeConstants).Resize(, 2)
    .AutoFilter Field:=1, Criteria1:="=" + myType
   .Columns(2).SpecialCells(xlCellTypeVisible).Copy
    With .Columns(3)
        .PasteSpecial xlPasteValues
        .RemoveDuplicates Columns:=Array(1), Header:=xlYes
    End With
    .AutoFilter
End With