Problem with date formatting

  • Thread starter Thread starter webels
  • Start date Start date
W

webels

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
 
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").NumberFormat), _
Operator:=xlAnd
 
As a double safety

Maybe try

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

--

HTH

Bob
Click to expand...

Thanks Bob
Will try this and see
 
As a double safety

Maybe try

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

--

HTH

Bob







- Show quoted text -
Click to expand...

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
 
As a double safety

Maybe try

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

--

HTH

Bob
Click to expand...

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