获取所有可能的过滤条件列表

From VBA I need to know the list of all criteria that I can choose in filtering columns. For example the following list: [1-ALFA;2-BETA;...5-ETC]

从VBA我需要知道我可以在筛选列中选择的所有条件的列表。例如以下列表:[1-ALFA; 2-BETA; ... 5-ETC]

1 个解决方案

#1

Say we have data like:

假设我们有以下数据:

and we filter size for large and we want to list the criteria for column A:

我们过滤大小的尺寸,我们想列出A列的标准:

Sub ShowCriteria()
    Dim r As Range, c1 As Collection, c2 As Collection
    Dim msg As String
    Set c1 = New Collection
    Set c2 = New Collection

    Dim LastRow As Integer

    With Worksheets("sheet1")
        LastRow = .Range("A" & Worksheets("sheet1").Rows.Count).End(xlUp).Row
    End With



    On Error Resume Next
    For Each r In Range("A2:A" & LastRow)
        v = r.Value
        c1.Add v, CStr(v)
        If r.EntireRow.Hidden = False Then
            c2.Add v, CStr(v)
        End If
    Next
    On Error GoTo 0

    msg = "Full criteria"
    For i = 1 To c1.Count
        msg = msg & vbCrLf & c1.Item(i)
    Next i

    msg = msg & vbCrLf & vbCrLf & "Visible criteria"
    For i = 1 To c2.Count
        msg = msg & vbCrLf & c2.Item(i)
    Next i

    MsgBox msg
End Sub

Will display: