Solved: Filter by Date Selection?

[Poundland]

Guys,

Can you help me, I have written some code to filter some data by date.

' Sets the Date criteria for searching
TDate = Format((Now - 2), "dd/mm/yyyy")
' Filters for all receipts that are older than 24 hours
Selection.AutoFilter Field:=7, Criteria1:="<=" & TDate

All data is in the correct format, and when the code is run the filter is activated with the relevant search criteria but nothing is displayed in the data file.

However when I go into the filter manually on the data file, all the coded criteria is correct and when I confirm manually the filtered data is displayed.

Why is the code not automatically displaying the data?

[Bob Phillips]

Filtering dates is fraught with problems. The way I do it is to set the format to the first data cell in the range being filtered.

Something like this



' Sets the Date criteria for searching
TDate = Format((Now - 2), Range("G2").NumberFormat)
' Filters for all receipts that are older than 24 hours
Selection.AutoFilter Field:=7, Criteria1:="<=" & TDate

[Poundland]

XLD,

Thanks for the advice, I tried it and have still got the same issue.

The code populates the filter criteria, but the filtered data will not display until I manually go into the filter and press OK.

Any thoughts?

[Bob Phillips]

can you post the workbook?

[Poundland]

can you post the workbook?

XL,

Attached is the workbook.

[Bob Phillips]

This seems to work



Dim TDate As String

' Sets the Date criteria for searching
TDate = Format((Now - 2), Range("G3").NumberFormat)
' Filters for all receipts that are older than 24 hours
Cells.AutoFilter Field:=7, Criteria1:=">=" & Str(1), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(Date - 2))

[Poundland]

XLD,

This works a treat. Thanks :clap: :clap:

Just for my understanding, what part of the code does what to make it work?

[Bob Phillips]

In this case it seems to be the testing a range. Obviously, as you don't really want a range, just less than yesterday, I forced it with a range of day 1 and yesterday.

As I said, dates are a problem in filters, so I have a few techniques tucked away.

[Poundland]

Thanks very much, it is appreciated.