VBA:计算在筛选器中选择的不同项目的数量

Question

在Excel中，我有一个标题筛选器，这个筛选器允许用户选择不同的区域，并显示与所选区域匹配的记录。我有以下代码，然后显示在过滤器中选择的值：

Dim ftrRegion As Filter
On Error GoTo stopError
Set ftrArea = ActiveSheet.AutoFilter.Filters(2)
Set ftrRegion = ActiveSheet.AutoFilter.Filters(1)

If ftrRegion.On Then
    Application.EnableEvents = False
    Debug.Print ftrRegion.Criteria1
    Range("E206").Value = Mid$(ftrRegion.Criteria1, 2)
    Range("F201").Value = "Region" & " " & Mid$(ftrRegion.Criteria1, 2)
Else
    Range("E206").Value = ""
End If

但这样做的问题是，如果用户选择了多个值，那么整个操作就不起作用，所以我想要的是一种计算用户在筛选器中选择的值的数量的方法，如果只是1，则通过if语句应用此代码。有人能帮帮忙吗。

Answer 1

变化

If ftrRegion.On Then

至

If ftrRegion.On And ftrRegion.Count = 1 Then

Answer 2

我还没有在最近的Office版本中测试过这一点，但它可能会有所帮助：

Function GetFilterSettings(oFilter As Filter) As String
   Dim sTemp           As String
   With oFilter
      If .On Then
         If IsArray(.Criteria1) Then
            sTemp = Join(.Criteria1, ", ")
         Else
            Select Case .Operator
               Case xlAnd
                  sTemp = .Criteria1
                  If Len(.Criteria2) > 0 Then _
                     sTemp = sTemp & " and " & .Criteria2
               Case xlBottom10Percent
                  sTemp = "Bottom n% - effectively" & .Criteria1
               Case xlTop10Items
                  sTemp = "Top n items - effectively " & .Criteria1
               Case xlBottom10Items
                  sTemp = "Bottom n items - effectively " & .Criteria1
               Case xlOr
                  sTemp = .Criteria1
                  If Len(.Criteria2) > 0 Then _
                     sTemp = sTemp & " or " & .Criteria2
               Case xlTop10Percent
                  sTemp = "Top n% - effectively " & .Criteria1
            End Select
         End If
      Else
         sTemp = "Off"
      End If
   End With
   GetFilterSettings = sTemp

End Function

因此，在普通模块中，您的代码将变为：

Dim ftrRegion As Filter
On Error GoTo stopError
Set ftrArea = ActiveSheet.AutoFilter.Filters(2)
Set ftrRegion = ActiveSheet.AutoFilter.Filters(1)

    Application.EnableEvents = False
    Range("E206").Value = GetFilterSettings(ftrArea)
    Range("F201").Value = "Region " & GetFilterSettings(ftrRegion)