Deselect cell value from filter using Macro

[Matt J R]

I can't seem to get this bit of my macro to work

ActiveSheet.ListObjects("PromoData").Range.AutoFilter Field:=13, Criteria1:=
"<>" & ThisWorkbook.Sheets("Command_Center").Range("M" & i).value, Operator:=xlFilterValues

I keep getting this error:

Compile error:


Expected: expression

I've looked all over and can't seem to find the solution. I'm sure I've missed something simple.
Any help is greatly appreciated.

 

[JLGWhiz]

Try dropping the 'xlFilterValues'. I don't believe it is needed for what you have.

 

[Matt J R]

Try dropping the 'xlFilterValues'. I don't believe it is needed for what you have.

You are right. I don't need that bit.

I got the first bit of code to work. Now this doesn't work. It follows the previous bit, and uses the same value. It gives me Run-Time Error '1004': If I replace the &ThisWork...... with the value in the cell it works. The values that are in the table are the same as on the Command_Center sheet. I checked the formatting..... I'm lost.

ActiveSheet.ListObjects("Manual_Goals").Range.AutoFilter Field:=2, Criteria1 _
    :="<>" & ThisWorkbook.Sheets("Command_Center").Range("M" & i).Value
[\Code]

 

[JLGWhiz]

When I am going to use a referenced value that takes a lot of space like that more than once in code I usually find it easier to assign it to a variable and use the variable in the statements. like

Dim crit As String
crit = ThisWorkbook.Sheets("Command_Center").Range("M" & i).Value
 ActiveSheet.ListObjects("Manual_Goals").Range.AutoFilter Field:=2, Criteria1:="<>" & crit

That way, it there is an error generated by that reference, I only have to fix it once, plus it saves typins in the statements. The runtime 1004 errors are usually some ridiculously simple thing like spelling, spaces, charactes, case etc. If You have your actual code split like the posted code, then the space between 'Criteria1' and ':=' is the problem. that has to all be grouped without a break. The line extender underscore should only be used where there is a space in the code character groups. Other than that, I don't see anything that would cause the error. Also you used a reverse slash instead of forward slash on you code tag, so it didn't take.

 

[Matt J R]

Thanks so much for the quick reply and thorough answer. I have to run some reports, but I'll give it a try in a little bit and let you know how it goes. This is the most ambitious I've tried to get with a macro. Really appreciate the help.

When I am going to use a referenced value that takes a lot of space like that more than once in code I usually find it easier to assign it to a variable and use the variable in the statements. like

Dim crit As String
crit = ThisWorkbook.Sheets("Command_Center").Range("M" & i).Value
 ActiveSheet.ListObjects("Manual_Goals").Range.AutoFilter Field:=2, Criteria1:="<>" & crit

That way, it there is an error generated by that reference, I only have to fix it once, plus it saves typins in the statements. The runtime 1004 errors are usually some ridiculously simple thing like spelling, spaces, charactes, case etc. If You have your actual code split like the posted code, then the space between 'Criteria1' and ':=' is the problem. that has to all be grouped without a break. The line extender underscore should only be used where there is a space in the code character groups. Other than that, I don't see anything that would cause the error. Also you used a reverse slash instead of forward slash on you code tag, so it didn't take.

 

[Matt J R]

Thanks so much for the quick reply and thorough answer. I have to run some reports, but I'll give it a try in a little bit and let you know how it goes. This is the most ambitious I've tried to get with a macro. Really appreciate the help.

When I tried the variable it wouldn't filter properly..... The row numbers would turn blue, but nothing would be filtered.... Really weird. I removed the "_" and all spaces, but that didn't seem to have any effect either way..... I think it is a bug or some kind of formatting issue.

Thanks for the help. I'll let you know if I ever get it figured out. The first error that I got was because the filter criteria was the only data in that column.

What I was essentially trying to do is break a master report out into sales regions.

 

[JLGWhiz]

Try the statement like this:

ActiveSheet.ListObjects("PromoData").UsedRange.AutoFilter 13, "<>" & Sheets("Command_Center").Range("M" & i).value

Just copy this and paste it into your code in place of the existing statement.

 

[Matt J R]

Try the statement like this:

ActiveSheet.ListObjects("PromoData").UsedRange.AutoFilter 13, "<>" & Sheets("Command_Center").Range("M" & i).value

Just copy this and paste it into your code in place of the existing statement.

I tried it, it didn't like the UsedRange part. But the other code that you suggested where you assigned the variable crit is working perfectly. I'm not entirely sure what my issue was last week. I'm sure it was operator error somehow.

Thanks again for your help!