help with autofilter code

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016
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
 
Last edited:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
The code works for me.
Maybe something wrong in your data.
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
Are there other filters active?
Do you delete all filters before this filter?
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016

ADVERTISEMENT

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

Well-known Member
Joined
Aug 4, 2017
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
the range is not the same: $A$1:$AY$4138 - $A$1:$AY$5000
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016

ADVERTISEMENT

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

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jun 10, 2016
Messages
280
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,948
Messages
5,621,777
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top