Advanced Filter not working correctly in macro

[Guest]

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

 

[Guest]

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

 

[Guest]

I'm using 2007 as well... do you think then that this is some sort of bug?

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Don Guillett]

try
"a9:" & lastcell

 

[Bob Phillips]

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)

 

[pkeegs]

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

 

[Ron de Bruin]

Hi pkeegs

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

 

[pkeegs]

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