Thanks Ron,

That has worked. It is also working with the CDate format dd/mm/yy in the
data entry Input Box which doesn't conflict with our local date format

Regards

"Ron de Bruin" wrote:

Hi pkeegs

Try to use the US date format
mm/dd/yyyy



--

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


"pkeegs" wrote in message ...
Hi Bob,

I have just moved to Excel 2007 and have struck the same problem as Tara on
code that worked well in 2003. I have added the code as suggested without
success. I have also reformatted all the date cells to ensure that is not a
problem. It does not seem to be recognising the criteria at all. As with
Tara, it is only the advance filter that is being affected. Even when I
create a separate Macro just to run the advanced filter and include your code
it still does not work. Any tips?

Regards

"Bob Phillips" wrote:

I got it to work by re-creating I2 in code.

Assuming your dates are in column A, and that as A( is the heading, A10 must
be the first, I added this before the advancedfilter

Range("I2").Value = "<" & Format(Range("H2").Value,
Range("A10").NumberFormat)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"OssieMac" wrote in message
...
Hi Tara,

I see what you mean. I have tested this in xl2007 and like you said it
works
perfectly in the interactive mode but the macro hides all rows.

I recorded a macro by simply selecting the data and performing the
Advanced
filter actions on it and it worked perfectly but not when I run the macro.

What version of xl are you using?

Regards,

OssieMac

"Tara H" wrote:

I have a workbook with a large amount of data. One of the columns
contains
dates. I want to display all of the rows with a date more than a month
ago
(to delete in a later step), and have used an advanced filter to do this.

I have the formula '=Today()-30' in cell H2. Cell I1 has the column
header
'Date' copied to it, and cell I2 has '="<"&TEXT(H2,"dd/mm/yyyy")'.

This works perfectly when I do it manually, but when I run a macro using:

Range("A9:LastCell").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("I1:I2"), Unique:=False

all of the rows are hidden.

'LastCell' is correctly named, by the following script:
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveWorkbook.Names.Add Name:="LastCell", RefersTo:=ActiveCell

If I break the code just before the line where the actual filtering
happens
and do the filter manually using '$A$9:LastCell' as the source data and
'Criteria' as the criteria, it works as I expect.

My headers are in row 9 (columns A to N) because of the way the report
comes
to me, I use some cells in the rows above to keep my criteria in.

I've tried everything I can think of - does anyone know what I'm doing
wrong
here?
Many thanks in advance for any help.

Tara H