自动筛选数组，不显示正确的值

Question

我试图在VBA中使用自动筛选，但当我尝试使用下面的代码时，它不显示筛选的值，它只显示空白。当我手动编写数组时，自动筛选功能正常工作。

Dim Filtersheet As Worksheet
Set Filtersheet = Worksheets("Filtersheet")
Dim str As String
Dim Arr As Variant
Dim Lastrow As Integer
Dim d As Long


Lastrow = Filtersheet.Cells(Filtersheet.Rows.Count, "B").End(xlUp).Row

With UserFormFilter
If .CheckBoxXX20.Value = True Then str = Chr(34) & "XX20" & Chr(34) & ","
If .CheckBoxY12.Value = True Then str = str & Chr(34) & "Y12" & Chr(34) & ","
If .CheckBoxZ1212.Value = True Then str = str & Chr(34) & "Z12/12" & Chr(34) & ","
If .CheckBoxXX10.Value = True Then str = str & Chr(34) & "XX10" & Chr(34) & ","
If .CheckBoxV12.Value = True Then str = str & Chr(34) & "V12" & Chr(34) & ","
If .CheckBoxZ2015.Value = True Then str = str & Chr(34) & "Z20/15" & Chr(34) & ","
If .CheckBoxXX1010.Value = True Then str = str & Chr(34) & "XX10/10" & Chr(34) & ","
If .CheckBoxY20.Value = True Then str = str & Chr(34) & "Y20" & Chr(34) & ","
If .CheckBoxZ2012Y20.Value = True Then str = str & Chr(34) & "Z20/12 & Y20" & Chr(34) & ","
End With

str = Left(str, Len(str) - 1)


Filtersheet.Range("A1", "t" & Lastrow).AutoFilter Field:=10, _ Criteria1:=Array(str), Operator:=xlFilterValues

Answer 1

您正在使用要查看的筛选器值创建一个(以逗号分隔)字符串，并将其传递给自动筛选器。您正在对该字符串使用Array-operator，但是，Array-operator仅看到一个值(str的内容)，并将其转换为仅包含一个元素的数组。例如，您正在过滤一个字符串"XX20","Y12" -这将导致一个空表。

您需要的是一个数组，其中列表的每个值都有一个条目。您可以不使用Array，而是使用Split-command，它将字符串拆分成多个片段。不需要使用引号字符，因为我们已经在处理字符串了。

str = ""
With UserFormFilter
    If .CheckBoxXX20.Value Then str = str & "XX20" & ","
    If .CheckBoxY12.Value Then str = str & "Y12" & ","
    (...)
End With
str = Left(str, Len(str) - 1)
Filtersheet.Range("A1", "t" & Lastrow).AutoFilter Field:=10, _ 
     Criteria1:=split(str, ","), Operator:=xlFilterValues