Filter to exclude data

Hi All,

I have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox
my code so far is:

'Inputbox to filter out new stores
Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120")
x = Split(Newstore, " ")
For i = 0 To UBound(x)

Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="<>" & Array(x) _
, Operator:=xlFilterValues
Next i

For some reason it doesnt like the "<>" & Array(x) Any ideas?

thanks
Sam

Hi All,
For some reason it doesnt like the "<>" & Array(x) Any ideas?


Hi Sam,

Do you mean this instead?
Criteria1:="<>" & x(i)

But I don't think that your code will do what you're hoping....

If you're trying to autofilter with more than 2 criteria for 1 field, then a common workaround is to have an additional column in the table containing formulas which will act as indicators on whether the rows should be hidden or not.

Okay, I've been wrangling with advanced filters for the last hour, and this does do what you're after, but I'm not entirely happy with it:
Dim strCriterion As String: strCriterion = "=or(label={""b"",""d""})"

With sheetFilter

.Range("A2").Value = ""
.Range("A3").Formula = strCriterion
Call .Range("A5:A10").AdvancedFilter(xlFilterInPlace, .Range("A2:A3"))

End With

This filters a list in A6:A10, with column label "label" in A5, to the elements that match the strings specified in strCriterion, i.e. "b" and "d". All one has to do if one wants to filter out certain elements is to wrap the OR() with a NOT().
The reason I'm not entirely happy is because this required the use of A2 and A3 as dummy cells to hold the formula, because the .AdvancedFilter method will only accept a Range object for the criterion argument, not a string or a Name. Is it possible to construct a "virtual Range" that doesn't exist on any worksheet but, well, somewhere "in the off"?