获取所有可能的过滤条件列表 [英] Get all list of possible filter criteria

问题描述

从VBA我需要知道我可以在过滤列中选择的所有标准的列表。例如以下列表：[1-ALFA; 2-BETA; ... 5-ETC]

解决方案

p>

，我们过滤尺寸为大，我们想列出 A 列的条件：

  Sub ShowCriteria（）
 Dim r As Range，c1 As Collection，c2 As Collection 
 Dim msg As String 
 Set c1 = New Collection 
设置c2 =新集合
 
 Dim LastRow As Integer 
 
带有工作表（sheet1）
 LastRow = .Range（A&工作表（sheet1）。Rows.Count）.End（xlUp）.Row 
结束
 
 
 
关于错误简历Next 
对于每个r in Range（A2：A& LastRow）
v = r.Value 
 c1.Add v，CStr（v）
如果r.EntireRow.Hidden = False然后
 c2.Add v，CStr（v）
 End If 
 Next 
 On Error GoTo 0 
 
 msg =完整条件
对于i = 1至c1.Count 
 msg = msg& vbCrLf& c1.Item（i）
 Next i 
 
 msg = msg& vbCrLf& vbCrLf& 可见标准
对于i = 1至c2.Count 
 msg = msg& vbCrLf& c2.Item（i）
下一个i 
 
 MsgBox msg 
 End Sub 
  

将显示：

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]

解决方案

Say we have data like:

and we filter size for large and we want to list the criteria for column 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:

这篇关于获取所有可能的过滤条件列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！