Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"


Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.


Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
...
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"


Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.


Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message
...
Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
...
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"


Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.


Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

On Jul 20, 9:34*am, "Bob Phillips" wrote:
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * * Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
* * * * Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message

...



Maybe try

Range("A2").AutoFilter Field:=1, _
* * * *Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
* * * *Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
....
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* *Columns("A:A").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 * and * 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Thanks Bob
Will try this and see

On Jul 20, 5:34*pm, "Bob Phillips" wrote:
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * * Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
* * * * Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message

...



Maybe try

Range("A2").AutoFilter Field:=1, _
* * * *Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
* * * *Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
....
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* *Columns("A:A").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 * and * 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie- Hide quoted text -

- Show quoted text -

Hi Bob
This didn't seem to work, I am still left with dates after the date in
"N1". I should explain myself better. I ahve dates in Column A and I
am trying to delete rows that have dates from Today() minus 56 days
inclusive.

Much appreciated
Eddie

On Jul 20, 9:34*am, "Bob Phillips" wrote:
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * * Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
* * * * Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message

...



Maybe try

Range("A2").AutoFilter Field:=1, _
* * * *Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
* * * *Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
....
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* *Columns("A:A").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 * and * 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Hi Bob
Just to let you know. I got this sorted, thanks for the lead..
Much appreciated
Eddie