VBA:AdvancedFilter在自动筛选后获得唯一值 [英] VBA: AdvancedFilter to get unique values after AutoFilter

问题描述

我正在尝试在过滤A列并将其粘贴到C列之后按以下方式在B列中获取唯一名称:

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

但是，这将绕过A列的过滤，并返回B列的所有唯一名称.

如何将Autofilter或AdvancedFilter用于多个条件?

谢谢

解决方案

您可以按照以下步骤进行操作

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

如您所见，实际的工作仅在一行中完成，其前面是两行设置"行(列"C"标题和过滤条件临时单元格)，然后是一行用于删除临时单元格./p>

如果您不能用Range("D1:D2")编写，则可以使用任何其他范围(前提是该范围为1列2行)，并相应地更改代码.

---

或者您可以采用复制"方法

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

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,

解决方案

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

这篇关于VBA:AdvancedFilter在自动筛选后获得唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！