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

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

On May 28, 9:55 am, wrote:
Hello all

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

I have used Ron de Bruin's excellent resource athttp://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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ups.com...
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