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
‘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
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
123456
123457
123458
123459
123444
111456
111588
Any idea why ?
Regards
CA Kanwaljit Singh Dhunna
Try this,
begins with = 123
In my EasyFilter add-in there are also a lot of Date filter options
http://www.rondebruin.nl/easyfilter.htm
If your checks are numbers (instead of text that looks like numbers), then I don’t think begins with will work. Try this
Hey Dick – Excel 2007 gives you the ability to select a month with just a sigle check of a checkbox. Check it out. Dave.
[…] Filter data by Months using Advanced Filters and VBA […]