Filter a Range, based on combobox value

[robertvdb]

I have a sheet with data for each day of the year. I want to use a combobox, in order to filter these data for a particular month.

So when the value in the combox changes, then the list needs to be filtered accordingly.

Thanks in advance.

 

[John_w]

For a form control combobox, assign this macro to it:

Public Sub Filter_By_Month()

    Dim monthCombo As DropDown

    With ActiveSheet
        Set monthCombo = .DropDowns(1)
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(DateSerial(Year(.Range("A2").Value), monthCombo.Value, 1)), _
                                       Operator:=xlAnd, Criteria2:="<=" & CLng(DateSerial(Year(.Range("A2").Value), monthCombo.Value + 1, 0))
    End With
    
End Sub

 

[robertvdb]

Eventually, I worked on a solution as below, and it works. Comments/Suggestions for improvement are welcome.

Private Sub monthCombo_Click()
Dim montH As String
Dim StartDate As String, EndDate As String

montH = monthCombo.Value
Select Case montH
    Case "January"

        'StartDate = DateSerial(2023, 1, 1)   this does not work, I defined the dates as below in the Select block
        'EndDate = DateSerial(2023, 12, 31)

        StartDate = "1/1/2023"
        EndDate = "1/31/2023"
    Case "February"
        StartDate = "2/1/2023"
        EndDate = "2/28/2023"
    Case "March"
        StartDate = "3/1/2023"
        EndDate = "3/31/2023"
    Case "April"
        StartDate = "4/1/2023"
        EndDate = "4/30/2023"
    Case "May"
        StartDate = "5/1/2023"
        EndDate = "5/31/2023"
    Case "June"
        StartDate = "6/1/2023"
        EndDate = "6/30/2023"
    Case "July"
        StartDate = "7/1/2023"
        EndDate = "7/31/2023"
    Case "August"
        StartDate = "8/1/2023"
        EndDate = "8/31/2023"
    Case "September"
        StartDate = "9/1/2023"
        EndDate = "9/30/2023"
    Case "October"
        StartDate = "10/1/2023"
        EndDate = "10/31/2023"
    Case "November"
        StartDate = "11/1/2023"
        EndDate = "11/30/2023"
    Case "December"
        StartDate = "12/1/2023"
        EndDate = "12/31/2023"
End Select

ActiveSheet.Range("$A$1:$C366").AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

End Sub

 

[John_w]

For an ActiveX combobox, which you seem to be using, named monthCombo, the ListIndex + 1 property gives the month number. Note I've used the Change event, rather than the Click event, so that the data is filtered only when you change the month.

Private Sub monthCombo_Change()
    With ActiveSheet
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(DateSerial(Year(.Range("A2").Value), monthCombo.ListIndex + 1, 1)), _
                                       Operator:=xlAnd, Criteria2:="<=" & CLng(DateSerial(Year(.Range("A2").Value), monthCombo.ListIndex + 2, 0))
    End With
End Sub

 

[robertvdb]

Thanks John ! Much better !