具有“开始于”的多个标准的VBA自动过滤器（文本过滤器） [英] VBA Autofilter (Text Filters) With Multiple Criteria of "begin with"

问题描述

自动过滤器字段：= 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:

这篇关于具有“开始于”的多个标准的VBA自动过滤器（文本过滤器）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！