如何使用VBA获得Excel 2016的过滤条件?

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.

提前谢谢你。

2 个解决方案

#1

I checked this question and 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

#2

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

代码应该是这样的。字段的代码是单元格(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

#1