具有“开始于”的多个标准的VBA自动过滤器（文本过滤器）

本文介绍了具有“开始于”的多个标准的VBA自动过滤器（文本过滤器）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 自动过滤器字段：= 1，标准1：=数组（ca *，inc *，ps *），运算符：$ {pre> ActiveSheet.Range（F_Item = xlFilterValues

我试图使用数组来筛选具有多个条件的列，条件是过滤值它以 ca 开头，或以 inc 开始，或以$ code> ps开始。

ActiveSheet.Range（F_Item）。AutoFilter字段：= 1，Criteria1：== ca * ，运算符：= xlOr，Criteria2：== inc *

这是这样工作的，

解决方案

有以下数据：

运行此宏：

Sub WildAutofilter（） Dim data As Range，c As Collection Dim v As String ，i As Long，ary 设置data = Range（A1：A23）设置c =新建C ollection On Error Resume Next For i = 2 To 23 v = Cells（i，1）.Value 如果Left（v，2）=ps 或左（v，2）=ca或左（v，3）=inc然后 c.Add v，CStr（v） End If Next i 错误GoTo 0 ReDim ary（0到c.Count - 1）对于i = 1 To c.Count ary（i - 1）= c 。$（$） $ b使用ActiveSheet.Range（$ A $ 1：$ A $ 23） .AutoFilter字段：= 1，Criteria1：=（ary ），运算符：= xlFilterValues 结束 End Sub

将产生：

ActiveSheet.Range("F_Item").AutoFilter Field:=1, Criteria1:=Array("ca*", "inc*", "ps*"), Operator:=xlFilterValues

I am trying to filter column with multiple criteria using array, the condition is to filter the values which starts with ca, or starts with inc, or start with ps.

ActiveSheet.Range("F_Item").AutoFilter Field:=1, Criteria1:="=ca*", Operator:=xlOr, Criteria2:="=inc*"

It's working this way, but its limited to search for two conditions only.

解决方案

With data like:

Running this macro:

Sub WildAutofilter() Dim data As Range, c As Collection Dim v As String, i As Long, ary Set data = Range("A1:A23") Set c = New Collection On Error Resume Next For i = 2 To 23 v = Cells(i, 1).Value If Left(v, 2) = "ps" Or Left(v, 2) = "ca" Or Left(v, 3) = "inc" Then c.Add v, CStr(v) End If Next i On Error GoTo 0 ReDim ary(0 To c.Count - 1) For i = 1 To c.Count ary(i - 1) = c.Item(i) Next i With ActiveSheet.Range("$A$1:$A$23") .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues End With End Sub

Will produce: