excel - 如何按月份名称过滤excel列？

Question

鉴于此列表在 excel 中：

当用户选择月份名称时，如何过滤行（基于出生日期列）？这是我到目前为止所做的，但我不知道如何操作该DOB列：

Private Sub ComboBox2_Change()
    On Error Resume Next
    If Me.ComboBox2.Value = "<<All>>" Or Me.ComboBox2.Value = "" Then
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Else
        Sheet1.Range("D4").AutoFilter field:=4, Criteria1:=Me.ComboBox2.Value
    End If
End Sub

我不知道我应该在 Criteria1 上放什么。

标签： excelvbafilter

Answer 1

使用自动筛选数月

• 使用宏记录器，我得出结论，介于和被设置的组合Criteria1将21按月过滤。32OperatorxlFilterDynamic
• 假设它ComboBox1在工作表上，并且代码在工作表的模块中。

按月份过滤

Option Explicit

Private Sub ComboBox1_Change()
    
    Me.AutoFilterMode = False
    
    If ComboBox1.Value <> "<<All>>" And ComboBox1.Value <> "" Then
        With Me.Range("A3").CurrentRegion
            .AutoFilter 4, ComboBox1.ListIndex + 20, xlFilterDynamic
        End With
    End If
    
End Sub

填充组合框

Sub populateComboBox()
    Const cList As String = "<<All>>,January,February,March,April,May,June," _
        & "July,August,September,October,November,December"
    Dim cData() As String: cData = Split(cList, ",")
    ComboBox1.List = cData
End Sub