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, exwrex...@westnet.com.au 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


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>