|
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
I'm using 2007 as well... do you think then that this is some sort of bug?
"OssieMac" wrote: 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 |
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
|
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
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 |
Advanced Filter not working correctly in macro
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 |
| All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com