Vba excel 2007 autofilter with one criteria which is a variable

[CARR65DEL]

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!

 

[CARR65DEL]

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!

 

[dmt32]

try it like this:

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

Dave

 

[CARR65DEL]

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

 

[Faintkitara]

try it like this:

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

Dave

This helped me too! Appreciate it!