Solved: Autofilter Question

[gibbo1715]

Hi All

Can anyone tell me why if I put in a custom auto filter to get all values below todays date it works fine but if I try the same via code it doesnt

Cells.Select
Selection.AutoFilter Field:=8, Criteria1:="<=19/01/2007", Operator:=xlAnd

Im actually trying to autofilter so I only see records <= now()

Any Ideas please

thanks

Gibbo

[Charlize]

maybe try it with a variable
criteria1:= "<=" & date
Charlize

ps. you don't have to select all of your cells. Just the toprow where autofilter is supposed to be. Even more just the cells were you want to filter on.

[RichardSchollar]

I really loathe using dates as criteria in Autofilter thru VBA. Try matching the format of the criteria string to how the date appears in the Excel data list. Eg so if your dates were in dd-mmm-yyyy then use:

Selection.AutoFilter Field:=8, Criteria1:="<=" & Format(Date,"dd-mmm-yyyy"), Operator:=xlAnd

Hope this helps!

Richard

[gibbo1715]

Thanks Guys

I ve tried that

Cells.Select
Selection.AutoFilter Field:=8, Criteria1:="<=" & Format(Now(), "dd/mm/yy"), Operator:=xlAnd
Range("A1").Select

But still doesnt work for me, however if i open the custom autofilter and re type the same contents as is already there it works fine??

Im a little confussed

In fact if i open the custom autofilter and then colse it it updates my worksheet??

Gibbo

[Bob Phillips]

With Cells
.AutoFilter Field:=8, Criteria1:="<=" & Format(Date, .Cells(2, 8).NumberFormat)
End With

[Charlize]

What about this one ?
Sub ByCellDateTimeless()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double
Dim rDate As Range, rTime As Range
Set rDate = Sheets(2).Range("A1") 'Cell housing date & time
If Not IsDate(rDate) Then 'Check if valid
MsgBox "Non valid date and time in Sheet1"
Exit Sub
End If

dDate = DateSerial(year(rDate), month(rDate), day(rDate))
lDate = dDate

Set rTime = rDate
dTime = TimeSerial(Hour(rTime), Minute(rTime), Second(rTime))
dblTime = dTime

dDateTime = lDate + dblTime
Range("B1").AutoFilter field:=1, Criteria1:="<" & dDateTime
End Sub

Charlize

[gibbo1715]

that did it

thanks

Paul