AutoFiltering on Months

I need a quick way to create a filter by month on an autofiltered range. Right now I’m manually entering this:

That’s tedious. One option is to create another column with just the month and year in it and filter on that, but I don’t like my data cluttered. So I wrote this macro that creates the filter. It filters the list to one month based on the date in the activecell.

Sub FilterOnMonth()
    ‘Create a filter in the active column within a sheet’s autofilter
   ‘Use the month of the activecell to filter the range
    Dim lMonth As Long
    Dim lYear As Long
    If IsDate(ActiveCell.Value) Then
        lMonth = Month(ActiveCell.Value)
        lYear = Year(ActiveCell.Value)
            ‘Check if there is an autofilter
           If ActiveCell.Parent.AutoFilterMode Then
                ‘Make sure activecell is within autofilter range
               If Not Intersect(ActiveCell, _
                    ActiveCell.Parent.AutoFilter.Range) Is Nothing Then
                    ‘Create filter
                   With ActiveCell.Parent.AutoFilter
                        .Range.AutoFilter ActiveCell.Column – .Range(1).Column + 1, _
                            “>=” & DateSerial(lYear, lMonth, 1), _
                            xlAnd, _
                            “<=” & DateSerial(lYear, lMonth + 1, 0)
                    End With
                End If
            End If
    End If
End Sub
Posted in Uncategorized

6 thoughts on “AutoFiltering on Months

  1. Hi Dick,

    I selected a column containing Cheque numbers (given below) and pressed Ctrl+F. When I try to find out cheque number using Wild Cards like 123???, it works perfectly. But when I activated the filters and tried to use Custom options on Filter dropdown, it filtered nothing. I have already tried

    Show rows where Cheque No.
    equals = 123???
    begins with = 123???
    contains = 123???

    Chq No

    Any idea why ?
    CA Kanwaljit Singh Dhunna

  2. Hey Dick – Excel 2007 gives you the ability to select a month with just a sigle check of a checkbox. Check it out. Dave.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.