Autofilter method of range class failed despite data match existing

Hi all,

The following code is returning the error 'autofilter method of range class failed'. I have defined the IncomeStartDate variable as Range("b19") on my validation sheet which contains a formula to display the value 01/01/2014. If I manually select the custom filter on the sheet in column G (7) using "is after of equal to" 01/01/2014 the sheet filters for those values, so why won't the code replicate this?

Please Login or Register  to view this content.

Any help would be greatly appreciated.

Regards,

T

Sadly I can't post the data. I have a feeling the problem lies in the formatting so will explore that avenue

Please Login or Register  to view this content.

returning no value and showing an error are two different things.
If the code error on this line, it is a syntax error, but if it does not show an error, but does nothing, it may be the code is not recognised the date and becomes a formatting issue.

The code error is on this line. But I'm sure there is no syntax error. I will try and upload a sample file as this is driving me crazy

Hmm, none of the above are working and I cant upload the file. At present the income start date is 01/01/2014. In column 7 of the activeD sheet I have over 5,000 entries beyond this date. They, and their entire row needs to be removed. Can anyone give me a piece of code to do this other than autofilter?

Thanks in advance

It is not working is a vague statement. Did you get the error or not?
You can probably resolve the issue by looking at

IncomeStartDate = Validation.Range("B19")

Go to the module itself, then open local window.
Step over the code by pressing F8 and see what value do you get when the code reaches this line.
I suspect the value of IncomeStartDate may be nil.

Yes I was still getting the error.

The value of IncomeStartDate was correct. Rather strangely, the code works fine if I use this:

Please Login or Register  to view this content.

However I have used the original code:

Please Login or Register  to view this content.

Several times before in slightly different circumstances and it has always run smoothly.

For the moment I will use the "A1:AF1" range but are there any ideas as to why this may have occured?

Okay! Try to use a different line.

Please Login or Register  to view this content.

Is the workbook .xls format or later?

File is .xlsm

AB33 your code produces the same error on this line

Please Login or Register  to view this content.

It still only runs if I use the "A1:Af1" range as above. Furthermore I have 65,000 rows so the delete method you use is quite lengthy.

But thank you for all the help so far

Do you have a Table or standard range?

Izandol I have the data in the form of a table query. Not a standard range.

AB33 I have also used the above code many times. What do you mean my cursor has to be on sheet 19? When I run the code due to the following lines:

Please Login or Register  to view this content.

the active sheet is sheet 27 as it is ">100K<60" but that shouldn't matter as I use

With ActiveD

Which is linked to sheet 19.

I've taken your advice with and changed the delete argument and its a little quicker - Thank you

You are using sheet index number as a sheet name. It only takes moving one sheet to left or right, to mess-up the order of the sheet index.
I suggest you should use a string name like sheets("sheet19") or code name of the sheet.
In this case, you can easily identify the error. If the sheet name (sheet19), does not exist on the sheet collection, you get an error of "Range out of script"

Ok thanks. I know this is not the error I'm dealing with now but it will definitely avoid confusion in the future!

If you have a Listobject you should use Listobjects(1).Range.Autofilter

Thanks. I don't believe I have a listobject.

A Table is a Listobject in VBA.

Problem solved. I copied the data pasted xlvalues to a new sheet and can now filter using .rows(1) or .range("A:AF") or in any way. I think the problem rested with it being a table.

Thanks for all the help

The problem relates to the combination of a Table and your code which specified a range passing the extent of the table. Stating a range within the table - one or more rows/columns - filters the whole table however it is better to use the Listobject.