Vba excel 2007 autofilter with one criteria which is a variable

CARR65DEL

New Member
Joined
May 15, 2013
Messages
3
Hi...

I'm trying to set the criteria field as not equal to my variable. I need the variable because this file will eventually be saved repeatedly as a new version with a different account number. I do not want to have to update code in each one. The code that I have works correctly to identify the criteria as = to the variable, but does not work as <> to the variable. I receive various error messages including "Expected:Named Parameter" and "Syntax Error." I've reviewed many posts and I don't see where I am going wrong.

I'd appreciate any assistance!

ActiveSheet.Range("A" & FirstRow & ":R" & LastRow).AutoFilter Field:=1, Criteria1:=CUSTODY

ActiveSheet.Range("A" & FirstRow & ":R" & LastRow).AutoFilter Field:=1, Criteria1:"<>CUSTODY"

Thank you!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi...

I'm trying to set the criteria field as not equal to my variable. I need the variable because this file will eventually be saved repeatedly as a new version with a different account number. I do not want to have to update code in each one. The code that I have works correctly to identify the criteria as = to the variable, but does not work as <> to the variable. I receive various error messages including "Expected:Named Parameter" and "Syntax Error." I've reviewed many posts and I don't see where I am going wrong.

I'd appreciate any assistance!

ActiveSheet.Range("A" & FirstRow & ":R" & LastRow).AutoFilter Field:=1, Criteria1:=CUSTODY

ActiveSheet.Range("A" & FirstRow & ":R" & LastRow).AutoFilter Field:=1, Criteria1:"<>CUSTODY"

Thank you!

My company's Operations & Innovations group was able to resolve part of the problem for me. My line of code to get the autofilter to work needs to read as:

ActiveSheet.Range("A" & FirstRow & ":R" & LastRow).AutoFilter Field:=1, Criteria1:="<>CUSTODY"

I'm still having an issue though with the variable CUSTODY. When it is inside the quotes, it does not pick up the value of the variable. I'd appreciate any thoughts!!!!

Thank you! :)
 
Upvote 0
try it like this:

ActiveSheet.Range("A" & firstrow & ":R" & lastrow).AutoFilter Field:=1, Criteria1:="<>" & CUSTODY

Dave
 
Upvote 0
Thank you, Dave!!!! That worked beautifully and now my agony is over. Well, until my next round of coding. Have a great day! :)
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top