如何使用 VBA 在 Excel 2016 中获取筛选条件？

How do I get Filter Criteria in Excel 2016 using VBA?

提问by Max Tither

I am working on an Excel 2016 VBA Macro that applies a filter to the headings column. Afterwards, the user applies the filter criteria. I would like to be able to in VBA retrieve the filter criteria that the user applied and save it to a string array. Is there a way to access the filter criteria?

我正在处理一个 Excel 2016 VBA 宏，它将过滤器应用于标题列。之后，用户应用过滤条件。我希望能够在 VBA 中检索用户应用的过滤条件并将其保存到字符串数组中。有没有办法访问过滤条件？

Thanks you in advance.

提前谢谢你。

采纳答案by mmurrietta

I checked this questionand pretty much copied the first part of the code, the only thing is you don't get the field that it is applied to which can be problematic.

我检查了这个问题并且几乎复制了代码的第一部分，唯一的问题是你没有得到它应用到的可能有问题的领域。

Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
    With .Filters
        ReDim filtarr(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filtarr(f, 1) = .Criteria1
                    Debug.Print .Criteria1
                    If .Operator Then
                        filtarr(f, 2) = .Operator
                        filtarr(f, 3) = .Criteria2
                        Debug.Print .Operator & ", " & .Criteria2
                    End If
                End If
            End With
        Next f
    End With
End With

回答by Neil Cothran

I'd like to add a bit to the discussion. I found this (and other excellent sources of help) when investigating how to "return" the filter status. In my case, I want to DISPLAY the filter status in a cell on a worksheet.

我想在讨论中补充一点。在调查如何“返回”过滤器状态时，我发现了这个（以及其他优秀的帮助来源）。就我而言，我想在工作表的单元格中显示过滤器状态。

As I said, this question and many others like it were quite useful. From that, I was able to build the function shown in the code below.

正如我所说，这个问题和许多其他类似的问题非常有用。从那以后，我能够构建下面代码中显示的函数。

I pass it the name of the Table for which I want the filter status... thus it's passed in as a RANGE and it then needs to look in the PARENT (sheet) for information. This is because there may be several Tables on the SHEET from which it comes, so I can't just use the SHEET itself to get Autofilter information.

我向它传递了我想要过滤器状态的表的名称......因此它作为一个范围传入，然后它需要在父（表）中查找信息。这是因为它来自的 SHEET 上可能有几个表，所以我不能只使用 SHEET 本身来获取自动筛选信息。

This works well, except for one thing: if the active cell on the worksheet is NOT within the table in question, the function will see the number of filters as zero (WholeTable.Parent.Autofilter.Filters.Count in the sample below). I do not understand why this is, nor how to prevent it. If the active cell IS within the table range, it works perfectly.

这很有效，除了一件事：如果工作表上的活动单元格不在相关表格中，则该函数会将过滤器的数量视为零（以下示例中的 WholeTable.Parent.Autofilter.Filters.Count）。我不明白为什么会这样，也不明白如何防止它。如果活动单元格在表格范围内，则它可以正常工作。

Any hints would be appreciated!

任何提示将不胜感激！

Code:

代码：

---

---

Public Function AutoFilterCriteria(ByVal WholeTable As Range) As String

On Error Resume Next

If WholeTable.Parent.AutoFilter Is Nothing Then                     ' if no filter is applied
    AutoFilterCriteria = "None"
    On Error GoTo 0
    Exit Function
End If

Dim LongStr As String, FirstOne As Boolean
LongStr = ""
FirstOne = False

Dim iFilt As Integer
For iFilt = 1 To WholeTable.Parent.AutoFilter.Filters.Count         ' loop through each column of the table
    Dim ThisFilt As Filter
    Set ThisFilt = WholeTable.Parent.AutoFilter.Filters(iFilt)      ' look at each filter
    On Error Resume Next
    With ThisFilt
        If .On Then
            If FirstOne Then LongStr = LongStr & " AND "            ' Get column title
            LongStr = LongStr & "[" & WholeTable.Parent.Cells(WholeTable.Row - 1, WholeTable.Column + iFilt - 1).Value & ":"
            On Error GoTo Handle
            If .Operator = xlFilterValues Then                      ' dont really care to enumerate multiples, just show "multiple"
                LongStr = LongStr & "<Multiple>]"
            ElseIf .Operator = 0 Then
                LongStr = LongStr & .Criteria1 & "]"
            ElseIf .Operator = xlAnd Then
                LongStr = LongStr & .Criteria1 & " AND " & .Criteria2 & "]"
            ElseIf .Operator = xlOr Then
                LongStr = LongStr & .Criteria1 & " OR " & .Criteria2 & "]"
            End If
            On Error GoTo 0
            FirstOne = True
        End If
    End With
Next

AutoFilterCriteria = LongStr
On Error GoTo 0
Exit Function

Handle:
AutoFilterCriteria = "! Error !"
On Error GoTo 0

End Function

回答by Dy.Lee

the code would to be like this. The code of field is cells(1, f).

代码将是这样的。字段代码为cells(1, f)。

Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
    With .Filters
        ReDim filtarr(1 To .Count, 1 To 4) ' change array
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filtarr(f, 1) = .Criteria1
                    filtarr(f, 4) = Cells(1, f) 'field
                    Debug.Print .Criteria1, Cells(1, f)
                    If .Operator Then
                        filtarr(f, 2) = .Operator
                        filtarr(f, 3) = .Criteria2

                        Debug.Print .Operator & ", " & .Criteria2
                    End If
                End If
            End With
        Next f
    End With
End With