单列中的多个条件筛选，数据表中并不总是存在条件

Question

我正在尝试使用4个单独的条件筛选一列。当我通过以下操作只使用两种生物时，我有工作代码：

Worksheets("Table1").Range("A1").AutoFilter Field:=3, Criteria1:="*crit1*", _
Operator:=xlOr, Criteria2:="*crit2*"

在上述情况下，crit1和crit2总是出现在字段3中。

我想添加两个传统的临界值，临界值3和临界值4。我尝试了以下操作，但无法使其工作：

Worksheets("Table1").Range("A1").AutoFilter Field:=3, Criteria1:=Array("*crit1*", "*crit2*", _
"*crit3*", "*crit4*"), _
Operator:=xlOr

此外，在我看来，如果该地区不存在其中一个标准，这将打破？I.e，一个缺少任何crit3出现的数据集。

任何帮助都将是伟大的！

谢谢，Chris

更新：谢谢你，dwirony！花了一些时间，但我现在已经开始工作了。我真的不明白为什么我需要在Set sht行和filter数组行中进行更改，但代码现在正在做我想要做的事情。感谢您的帮助！

Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = Worksheets("Table1")
lastrow = sht.Cells(sht.Rows.Count, "C").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("crit1", "crit2", "crit3", "crit4")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

For i = 2 To lastrow
If InStr(sht.Cells(i, 3).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 3).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i

Next k

'Filter on array
Worksheets("Table1").Range("A1").AutoFilter Field:=3, Criteria1:=Array(filterarr), Operator:=xlFilterValues

Answer 1

在这里，我已经为您的代码修改了它——只需将crit1、2、3和4替换为您的实际标准：

Option Explicit
Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = ThisWorkbook.Worksheets("Table1")
lastrow = sht.Cells(sht.Rows.Count, "C").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("crit1", "crit2", "crit3", "crit4")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

For i = 2 To lastrow
If InStr(sht.Cells(i, 3).Value, tofindarr(k)) > 0 Then
filterarr(j) = sht.Cells(i, 3).Value
j = j + 1
ReDim Preserve filterarr(0 To j)
End If
Next i

Next k

'Filter on array
sht.Range("$C$1:$C$" & lastrow).AutoFilter Field:=3, Criteria1:=Array(filterarr), Operator:=xlFilterValues
End Sub