How to filter an excel column by Month name?

user_6107813 0
Viewed 329

Given this list in excel:

enter image description here

How can I filter the rows (base on the date of birth column) when a user selects the name of the month? This is what I have done so far but I have no idea how to manipulate the DOB column:

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

I don't have any idea what should I put on Criteria1.

autofilter filter vba excel
2 Answers

Using AutoFilter for Months

  • Playing with the macro recorder, I concluded that the combination of Criteria1 being between 21 and 32 and Operator being set to xlFilterDynamic will filter by month.
  • It is assumed that ComboBox1 is on a worksheet and the code is in the worksheet's module.

Filter by Month

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

Populate Combo Box

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
user_98140690

First you sould in your code convert the month selected into a value (2 for february for example). Then you should probably extract the month from the DOB colmun by using a for loop like this (where selected month is the number of the corresponding month selected in the combobox):

   Dim current_month
    For i=4 To 13 
        current_month = Month(Range("D"&i).value)
        If current_month <> selected_month Then 'The month don't correspond to the selected
            Range("D"&i).EntireRow.Hidden = True 'Hide the row
    next i

Let my know if it helps.

user_149898480
Related