Tara,

I am struggling to get my head fully around it, but I came at this solution
from Autofilter. I have had problems with autofilter because it is doing a
string comparison, and because VBA dates are not handled exactly the same as
dates in Excel, and I have had to force the item being compared to be of
exactly the same format as the data being filtered. Presumably Advanced
filter is doing a similar sort of string comparison (so as to accommodate <
etc.), and so you need the same sort of solution.

Excel doesn't need the same constraints as Excel does all of the date
manipulation and converts the criteria to an integer number representing the
dates and manages the operator as well. I guess we are effectively doing
that within VBA being passing it back (to a lower level of) Excel.

--
HTH

Bob

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

"Tara H" wrote in message
...
Fabulous!

Thanks Bob, that did the trick perfectly. Do you have any idea what
particularly about the contents of I2 was causing the trouble?

Many thanks,
Tara H

"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