I do this using a 'helper' column.
First, set it up;
In one cell (C1 for my example) user enters the year "2009' for example
Another Cell (D1 for my examplel) contains a formula to get the first required date in the year (for example,'=DATE($C$1,1,1)' will give Jan 1st and the year returned as a date)
Another Cell (E1 in my example) contains the formula for the last date required in the year =DATE($C$1,12,31)
Next, in a column somewhere to the right of your data write a formula to return TRUE or FALSE if your dafalls bettween required dates - =AND(A8>=$D$1,A8<=$E$1)
Now set up autofilter on that column to hide show TRUE.
That will work the first time you set a year then apply the autofilter but you want that to happen automatically, so:
After setting up your autofilter, some VBA will make the filter change whenever the year cell is changed.
Right-Click the tab of the require sheet
Click View Code
Paste the following
Make sure you update the cell reference from C1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(ActiveCell, Range("C1")) Is Nothing Then
' Change C1 to the cell the user completes the year in
On Error Resume Next ' will stop the code break if autofilter is off
ActiveSheet.AutoFilter.ApplyFilter
On Error GoTo 0
End If
End Sub
Note: above code is set to change the filter only when the year cell is changed so it will not annoy when adding new records (e.g. if you added a new record outside the date year or the filter it would immeidately hide that line.
You could modify the range to a column address if you wish for example
If Intersect(ActiveCell, Range("G:G")) Is Nothing Then
Would run the code when something in column G changes.
Set the width of the column that returns TRUE FALSE if you wish, put it well to the right out of sight to users or both. Protect, and hide year start and year end dates as you wish.
<colgroup><col width="35"><col span="2" width="75"></colgroup><tbody>
</tbody>