Excel VBA-从自动筛选器返回Criteria1数组 [英] excel VBA - return Criteria1 Array from an Autofilter

问题描述

我有一个表，我需要返回所有已切换的值.当我选择了我想要的宏之后录制宏时，看起来像这样

I have a table that I need to return all of the toggled values. When i record a macro after selecting which ones i want it looks like this

ActiveSheet.Range("$A$1:$P$1000").AutoFilter Field:=6, Criteria1:=Array("A" _
    , "B", "C", "D", "E", "G"), Operator:=xlFilterValues

我遇到的问题是，将由用户过滤的a，b，c等值将始终在变化，因此我无法以这种方式对任何标准进行硬编码.

the problem i have is that the a,b,c, etc values that will be filtered by the user will always be changing so I can't hardcode any criteria that way.

有没有办法我可以返回一个类似于其外观的切换数组?

is there a way i can return an array of what is toggled on in a fashion similar to how this looks?

msgbox ActiveSheet.Range("$A$1:$P$1000").criteria1

推荐答案

以类似这样的数据开头:

Beginning with data like:

此子项应用过滤器:

Sub Macro1()
    Range("A1:C22").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$22").AutoFilter Field:=3, Criteria1:=Array( _
        "Alice", "Boris", "Mike"), Operator:=xlFilterValues
End Sub

此子项将:

• 确定要过滤的列
• 已进行选择的已过滤列中的
• 列出实际选择

Sub FilterInformation()
    Dim st As String, ws As Worksheet, rg As Range, boo As Boolean

    Set ws = ActiveSheet
    On Error GoTo GetMeOut
    Set rg = ws.AutoFilter.Range

    MsgBox "Filter range" & vbCrLf & rg.Address

    N = ws.AutoFilter.Filters.Count
    MsgBox "Number of filters" & vbCrLf & N

    For i = 1 To N
        boo = ws.AutoFilter.Filters.Item(i).On
        MsgBox i & "==>" & boo
        If boo Then
            MsgBox UBound(ws.AutoFilter.Filters.Item(i).Criteria1) & " items in array"
            U = UBound(ws.AutoFilter.Filters.Item(i).Criteria1)
            L = LBound(ws.AutoFilter.Filters.Item(i).Criteria1)
            For j = L To U
                MsgBox ws.AutoFilter.Filters.Item(i).Criteria1(j)
            Next
        End If

    Next
    Exit Sub
GetMeOut:
    MsgBox ("no filters in sheet")
End Sub

这篇关于Excel VBA-从自动筛选器返回Criteria1数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！