Filter Sheet With date

[adamsm]

Hi anyone,

I'm trying to figure out a code that would filter the sheet when date is written in cell I2 of the active sheet.

I'm having my dates written in column B starting from row 4 onwards.

Lets say for example if I have the date 18/1/2011 and 19/1/2011 in column B.

I'm trying to figure out how to filter the sheet with the date written in cell I2.

I hope I've made my question clear.

Any help on this would be kindly appreciated.

Thanks in advance.

[p45cal]

Take a look at the attached which has the following in the first sheet's code module:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$2" Then
Range("B3:C37").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:F2"), Unique:=False
End If
End Sub[/vba]The data range could be a list (aka table post xl2003) which would mean it would still all be filtered if the list was added to. I've done this on the second sheet with this code behind it:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$2" Then
ListObjects("List1").Range.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:F2"), Unique:=False
End If
End Sub[/vba] You could even make it a dynamic named range instead.
The criteria range doesn't need to be on the same sheet.

You can do something similar with an autofilter but the code would be a bit more complex (to make it robust).

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I2" '<<<< change to suit
Dim rng As Range
Dim Lastrow As Long

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Lastrow = Me.Cells(Me.Rows.Count, "B").End(xlUp).Row
Set rng = Me.Range("B1").Resize(Lastrow)
rng.AutoFilter field:=1, Criteria1:=Format(Target.Value, Me.Range("B2").NumberFormat)
Set rng = rng.Offset(1, 0).Resize(Lastrow - 1)
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[adamsm]

Thanks for the help p45cal & xld. I do really appreciate your help. Once again thanks.