在 Excel VBA 中，如何保存/恢复用户定义的过滤器? [英] In Excel VBA, how do I save / restore a user-defined filter?

问题描述

如何使用 VBA 保存并重新应用当前过滤器?

How do I save and then reapply the current filter using VBA?

在 Excel 2007 VBA 中，我正在尝试

In Excel 2007 VBA, I'm trying to

1. 保存用户在当前工作表上的任何过滤器
2. 清除过滤器
3. 做事"
4. 重新应用保存的过滤器

推荐答案

看看 捕获自动过滤状态

为了防止链接腐烂，这里是代码(感谢原作者):

To prevent link rot, here is the code (credit to original author):

适用于 Excel 2010，只需删除标记的注释行.

Works with Excel 2010, just delete the commented line marked.

Sub ReDoAutoFilter()
    Dim w As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String
    Dim col As Integer

    Set w = ActiveSheet

    ' Capture AutoFilter settings
    With w.AutoFilter
        currentFiltRange = .Range.Address
        With .Filters
            ReDim filterArray(1 To .Count, 1 To 3)
            For f = 1 To .Count
                With .Item(f)
                    If .On Then
                        filterArray(f, 1) = .Criteria1
                        If .Operator Then
                            filterArray(f, 2) = .Operator
                            filterArray(f, 3) = .Criteria2 'simply delete this line to make it work in Excel 2010
                        End If
                    End If
                End With
            Next f
        End With
    End With

    'Remove AutoFilter
    w.AutoFilterMode = False

    ' Your code here

    ' Restore Filter settings
    For col = 1 To UBound(filterArray(), 1)
        If Not IsEmpty(filterArray(col, 1)) Then
            If filterArray(col, 2) Then
                w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1), _
                Operator:=filterArray(col, 2), _
                Criteria2:=filterArray(col, 3)
            Else
                w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1)
            End If
        End If
    Next col
End Sub

这篇关于在 Excel VBA 中，如何保存/恢复用户定义的过滤器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！