Autofilter More Than 2 Items

[honkin]

I have a VBA macro which does a number of things, but I have not been able to get it to autofilter for more than 2 things, but I need it to do 4

Here is the macro

Sub Low_Risk_Lays()
'
' Low Risk Lays Macro
' This macro will filter for Remove VDW Rank 1, Class, Distance <=1650, # of Runners <=9, Exclude Brighton, Yarmouth, Windsor & Wolverhampton
'
    With ActiveSheet
        With .Cells(1).CurrentRegion
            .AutoFilter Field:=4, Criteria1:="<=9"
            .AutoFilter Field:=11, Criteria1:="<=1650"
            [B].AutoFilter Field:=8, Criteria1:="<>Brighton", Criteria2:="<>Yarmouth", Operator:=xlAnd[/B]
            .AutoFilter Field:=29, Criteria1:="<>1"
            .HorizontalAlignment = xlCenter
        End With
        .Columns("C:C").EntireColumn.Hidden = True
        .Columns("G:G").EntireColumn.Hidden = True
        .Columns("I:I").EntireColumn.Hidden = True
        .Columns("L:L").EntireColumn.Hidden = True
        .Columns("N:W").EntireColumn.Hidden = True
        .Columns("Y:AB").EntireColumn.Hidden = True
        .Columns("AD:AJ").EntireColumn.Hidden = True
        .Columns("AO:AO").EntireColumn.Hidden = True
        .Columns("AQ:BQ").EntireColumn.Hidden = True
        .Columns("BT:CP").EntireColumn.Hidden = True
        .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("New Results File.xlsm").Sheets("Low Risk Lays").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

The line which is in bold filters for the words Brighton and Yarmouth, but I actually need it to filter for an additional 2 words; Yarmouth & Wolverhampton

How is this possible at all?

Thanks in advance

 

[Fluff]

You cannot use autofilter to filter more than two criteria, unless you are doing an exact match.
One option would be to put a formula in an unused column that indicates if the row should be visible & filter on that.

 

[Yongle]

For info
<vba/> does not allow formatting

'this happens
[B]range("A1")=10[/B]

<rich/> does allow formatting

'this happens
range("A1")=10

 

[jasonb75]

Three ways that I can think of

1- Advanced filter (I think that this allows for more than 2 exclusions but not 100% certain).
2- Use a helper column with a formula.
3- Create an array of the remaining entries in the column, then filter on the array as inclusive (arrays can not be used as <> criteria).

 

[honkin]

You cannot use autofilter to filter more than two criteria, unless you are doing an exact match.
One option would be to put a formula in an unused column that indicates if the row should be visible & filter on that.

cheers Fluff

Unfortunately a fresh data file is downloaded each day, so not really feasible to add a helper column.

Thanks for replying

 

[honkin]

Three ways that I can think of

1- Advanced filter (I think that this allows for more than 2 exclusions but not 100% certain).
2- Use a helper column with a formula.
3- Create an array of the remaining entries in the column, then filter on the array as inclusive (arrays can not be used as <> criteria).

Thanks for your reply jasonb75

Neither 2 nor 3 are feasible as a new file is downloaded each day. Too cumbersome to add that each day

With 1, how would one use Advanced Filters to automatically filter a file which is newly downloaded each day?

cheers

 

[honkin]

For info
<vba/> does not allow formatting

'this happens
[B]range("A1")=10[/B]

<rich/> does allow formatting

'this happens
range("A1")=10

cheers Yongle

Not really sure what you are referring to. No formatting is required, so I have no idea what your code refers to

Thanks for the reply

 

[Yongle]

Not really sure what you are referring to. No formatting is required, so I have no idea what your code refers to

Read your own post
You tried to use bold in this line of your code

 [B].AutoFilter Field:=8, Criteria1:="<>Brighton", Criteria2:="<>Yarmouth", Operator:=xlAnd[/B]

It did not work
Now read my post again

 

[honkin]

You may actually need to reread the post yourself Yongle. You'll see this, "The line which is in bold filters for the words......" is what is written. What did you think that line referred to? I created the post, put the code in and then highlighted that line to indicate which line was in question for any prospective people wishing to assist. Unfortunately when everything was enclosed in VBA code tags, it stripped out the bold formatting and just dumped the . I didn't notice the change as I click post

 

[Yongle]

You may actually need to reread the post yourself Yongle. You'll see this, "The line which is in bold filters for the words......" is what is written. What did you think that line referred to? I created the post, put the code in and then highlighted that line to indicate which line was in question for any prospective people wishing to assist. Unfortunately when everything was enclosed in VBA code tags, it stripped out the bold formatting and just dumped the . I didn't notice the change as I click post

I tried to help you in post#3 explaining how you could avoid making the same mistake in future
In post #7 you replied saying you did not know what I was referring to
In post #9 (quoted above) you contradicted what you said in post#7
I am confused
Good luck resolving your issue