Autofiltering by Date Range entered by User

[exwrexona]

Hello all

I have been struggling with filtering some data based on two criteria.

I have used Ron de Bruin's excellent resource at http://www.rondebruin.nl/copy5.htm
as a base but unfortunately I can't seem to get it to work on showing
me the data where a date falls between two ranges.

I have a worksheet containing data in Columns A to M. Column I
contains "Yes" or is blank. Column K contains a Date in DD/MM/YYYY
format or is blank.

I need to be able to create a new worksheet with records copied from
the original sheet that have only those records that contain both Yes
from column I and falls within a date range specified by the user.

Filtering by Yes is no dramas. As soo as I try to filter on date I
end up with the headers on a new sheet but no records at all
regardless of whether it is a single date or a range.

One of my attempts includes the code:

rng.AutoFilter Field:=11, Criteria1:="> 2007/05/11", _
Operator:=xlOr, Criteria2:="< 2007/05/25"
rng.AutoFilter Field:=9, Criteria1:="=Yes"

Any suggestions?

Thanks in advance

David

 

[paul.robinson]

Hi
Filtering on dates searches for text strings, not Dates as such. Your
filter is looking for a string like "2007/05/11", in a column with
date format DD/MM/YYYY, which is not the same kind of string. So
either change your filter to match the date format or vice versa.
regards
Paul

 

[Ron de Bruin]

Hi David

You can also use this from my site

rng.AutoFilter Field:=4, Criteria1:=">=" & DateSerial(1947, 2, 23), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format


And try EasyFilter there are a lot of Date filter options in this add-in
http://www.rondebruin.nl/easyfilter.htm