help with autofilter code

[jordanburch]

Hey guys,

I have this code that filters for non blanks and non na values. It worked before but keeps erroring out. any ideas?


ActiveSheet.Range("$A$1:$AY$5000").AutoFilter Field:=50, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues


i get run time error 1004

autofilter method of class failed


Jordan

 

[mart37]

The code works for me.
Maybe something wrong in your data.

 

[jordanburch]

The code works for me.
Maybe something wrong in your data.

thanks for the reply. its looking at a match formula that returns a row, basically if it has a number I want to keep those number and filter only on those values. Not sure why its not working. any ideas?

 

[mart37]

Are there other filters active?
Do you delete all filters before this filter?

 

[jordanburch]

Are there other filters active?
Do you delete all filters before this filter?

yes there is another filter that is active before this one

ActiveSheet.Range("$A$1:$AY$4138").AutoFilter Field:=8, Criteria1:=Array( _
"DDARS", "DLA EBS", "MOCAS"), Operator:=xlFilterValues

that happens beforehand.

Jordan

 

[mart37]

the range is not the same: $A$1:$AY$4138 - $A$1:$AY$5000

 

[jordanburch]

the range is not the same: $A$1:$AY$4138 - $A$1:$AY$5000

ok this is what i changed it to


ActiveSheet.Range("$A$1:$AY$5000").AutoFilter Field:=50, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues

ActiveSheet.Range("$A$1:$AY$5000").AutoFilter Field:=8, Criteria1:=Array( _
"DDARS", "DLA EBS", "MOCAS"), Operator:=xlFilterValues


its still giving me the same error message. is it because the headers dont have that filter button at the top?

 

[jordanburch]

the range is not the same: $A$1:$AY$4138 - $A$1:$AY$5000

ok so i fixed the range. Do the headers need to have a filter on it? It is the same range all across the script. Would you like me to post the whole script?

 

[mart37]

It is strange that some headers haven't filters on.
You can test the macro step by step.
Make from all the filter lines comments (place before the line ')
Activate the first line. (remove the ')
Check the result.
Activate the second line.
Check the result.
Etc.

 

[jordanburch]

ok so funny thing is i ran it today just fine lol no changes. I honestly think since i have 32 bit excel and it only uses 2 gb of ram it was causing random errors like that. Hopefully you know what im talking about and have experienced the same.