Autofilter More Than 2 Items

honkin

Active Member
Joined
Mar 20, 2012
Messages
371
Office Version
  1. 2016
Platform
  1. MacOS
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

VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
Based on your reply I would say that the advanced filter would be the least feasible of the 3 methods, personally I would use the array method. This is the vba equivalent of using the dropdown checkboxes in the worksheet filter.

@Fluff What would be the most efficient way to create an array of unique values from a column of data, with the items to exclude removed? I was thinking collection or dictionary, but I'm not confident with the syntax of either.
 
Upvote 0
I would normally use a dictionary, but they don't work on Macs.
 
Upvote 0
I'm still a bit puzzled reading this thread why it is too cumbersome/not feasible to add the extra column and formula each day when using VBA.
 
Upvote 0
Unfortunately a fresh data file is downloaded each day, so not really feasible to add a helper column.
You add the helper column and formula with the macro, a lot easier than advanced filter.
 
Upvote 0
How about
VBA Code:
   With ActiveSheet.Range("A1").CurrentRegion
      With .Resize(, .Columns.Count + 1)
         With .Cells(2, .Columns.Count).Resize(.Rows.Count - 1)
            .FormulaR1C1 = "=if(or(rc8={""Brighton"",""Yarmouth"",""Windsor"",""Wolverhampton""}),""X"","""")"
            .Value = .Value
         End With
         .AutoFilter Field:=4, Criteria1:="<=9"
         .AutoFilter Field:=11, Criteria1:="<=1650"
         .AutoFilter .Columns.Count, "<>X"
         .AutoFilter Field:=29, Criteria1:="<>1"
         .HorizontalAlignment = xlCenter
      End With
 
Upvote 0
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 o_O
Good luck resolving your issue :)
Not really sure where you imagined I contradicted anything mate, so I'll explain in plain English. NO formatting is required in my code; never was and never will be. Before I enclosed the VBA code in the code tags, I made one line bold to highlight it to show what words I needed to filter for in the code. That bolding is not required in my code, nor does it exist in my code in Excel. I merely attempted to bold a single line in the post here on this forum. Prior to adding the code tags, the line appeared in bold and everything was fine. Then I enclosed the whole area of code in VBA code tags and did not notice that instead of that line remaining bold, it actually put the visible bold tags at either end.

You incorrectly assumed that I actually wanted the bold characters in my VBA code (why would anyone want to do that?), but a cursory glance at the original post and the wording of the text directly below the code would have shown you it was a reference to bolding a line of code to merely clarify what was required.

You exacerbated your mistake by then telling me, rather condescendingly, to read my own post and then this last reply, where you had the gall to say, "how you could avoid making the same mistake in future". I didn't make a mistake, except possibly in not noticing that this forum platform changed my bolded line in the post once the VBA tags were inserted. I mean seriously, I can't think of anyone who would enclose a line of VBA code in bold tags in Excel.....there is no logical reason for this and it is NOT what I did. I tried to make things clearer for anyone thinking to help, but instead got a lecture about something I did not do.

Thankfully others have been kind enough to offer solutions which may well work, but I'll thank you to keep your lectures to yourself.
 
Upvote 0
How about
VBA Code:
   With ActiveSheet.Range("A1").CurrentRegion
      With .Resize(, .Columns.Count + 1)
         With .Cells(2, .Columns.Count).Resize(.Rows.Count - 1)
            .FormulaR1C1 = "=if(or(rc8={""Brighton"",""Yarmouth"",""Windsor"",""Wolverhampton""}),""X"","""")"
            .Value = .Value
         End With
         .AutoFilter Field:=4, Criteria1:="<=9"
         .AutoFilter Field:=11, Criteria1:="<=1650"
         .AutoFilter .Columns.Count, "<>X"
         .AutoFilter Field:=29, Criteria1:="<>1"
         .HorizontalAlignment = xlCenter
      End With
Excellent Fluff

Yes, the reason I said the helper column was not feasible was I had no idea how to implement it and had visions of manually adding a column etc each day. If this works, then it is an all-in-one solution, as the original file is only used to grab the required data, so how it looks in the end is not so important.

I'll try it later today and let you know how it goes

Thanks again
 
Upvote 0
Not really sure where you imagined I contradicted anything mate, so I'll explain in plain English. NO formatting is required in my code; never was and never will be. Before I enclosed the VBA code in the code tags, I made one line bold to highlight it to show what words I needed to filter for in the code.

That is where you contradicted yourself, you tried to make one line bold and it didn't work, later you said that was not what you were trying to do. If that was the case then why did you try it to begin with?

@Yongle's first reply was (with good intentions) showing you how you can highlight code when you post it instead of it substituting the format with tags. Tags in place of formatting are more of an annoyance than a help, they don't stand out very well, especially with longer pieces of code. While you only used simple formatting I looked at one post recently where the user had tried to apply colour formatting to individual keywords within a line of code rather than the whole line, this made it an illegible mess.

Personally, I would say that it is preferable to use comments within the code to draw attention to problem lines rather than trying to format it in your posts, that way if we copy it over to the code editor we can still easily see where the problem is.
 
Upvote 0
Thank you @jasonb75 for clarifying things (y)

@honkin
I apologise if you feel that any of my posts above contains a lecture - none of them do, but I do accept that is how they were interpreted by you :eek:
This is a public forum and everyone is permitted to write anything they want within the remit of forum guidelines and rules. ✔
I do not react to hostile argumentative posts - I assume it's the frustrations with Excel being manifested. :mad:
Reacting badly is a total waste of energy. ?
My replies to you have always been very polite and you could consider extending others the same courtesy in future ;)
If that was a lecture from me - consider it your last o_O
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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