具有多个标准的Excel AutoFilter [英] Excel AutoFilter with Multiple Criteria

问题描述

我正在尝试创建一个自动过滤器，将执行以下操作

I am trying to create an autofilter that will do the below

开始与4或
包含2TWH或
包含2TER

Begins with "4" or Contains "2TWH" or Contains "2TER"

我相信会出现这样的样子

I believe it will turn out to look like this

ActiveSheet.Range("$A$5:$H$10").AutoFilter Field:=2, Criteria1:="=4*", _
    Operator:=xlOr, Criteria2:="=*2TWH*" 

但我需要以某种方式也有一个标准3 for == 2TER

but I need to somehow also have a criteria 3 for ="=2TER"

我做了一些搜索，看到你可以将值存储到数组中，然后调用数组值，但是我无法将它们存入数组中有一种可用的方式。

I did some googling and saw that you can store the values into an array and then call the array values but I was unable to get them into the array in a usable fashion.

有人能够提供一些帮助吗？

Is anyone able to offer some assistance with this please?

推荐答案

这应该是诀窍：

This should do the trick :

ActiveSheet.Range("$A$5:$H$10").AutoFilter Field:=2, Criteria1:="=4*", _
    Operator:=xlOr, Criteria2:=Array("*2TWH*","*2TER*")

这个doe没有任何错误，但...

它将只需要2个标准，因为有通配符（ * ）。 / strong>

这是数组中的最后一个条件（这里是 * 2TER * ）

This doesn't throw any error, but ...
it'll only take 2 criteria because there are wildcards (*).
Here it's the last criteria from the array (here *2TER*)

理想情况下，这可能是很好的，因为它适用于常量，

，但因为使用WildCard * ）它只支持2个条件...：/

Ideally, this would have been nice, as it works for constants,
but because you use WildCards (*) it only supports 2 criteria... :/

ActiveSheet.Range("$A$1:$H$10").AutoFilter Field:=2, Criteria1:=Array("*2TWH*", "*2TER*", "4*"), _
    Operator:=xlFilterValues

---

---

所以你必须使用 AdvancedFilter ：

---

---

So you'll have to go with AdvancedFilter :

With ActiveSheet
    'Const xlFilterInPlace = 1
    .Range("$A$5:$H$10").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=.Range("Z1:Z4")
End With

AdvancedFilter 将范围作为条件输入， CriteriaRange ，所以你必须放在一张单上：

• 您要应用于


• 您相应标题下方的条件的过滤器列的标题（如果您有倍数列）

• the header of the column you want to apply to the filter on
• your criteria below the respective header (if you have multiples columns)

该范围的每个 COLUMN 是 strong> AND

该范围的每个 ROW 都是通过 OR 链接

请仔细地构建表格

Each COLUMN of that Range, are linked by an AND
Each ROW of that Range, are linked by an OR
So build your table carefully!

在上面的示例代码中，我使用了：

（假设您的列的标题是要过滤的列）：

In the above example code, I have used :
(let's say that your column's header was Column To Filter On) :

Z1 | Column To Filter On
Z2 | 4*
Z3 | *2TWH*
Z4 | *2TER*

这篇关于具有多个标准的Excel AutoFilter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！