How to include an "AND" operator and Exclusions in AdvancedFilter with one criteria column in VBA

Question

First time poster so I apologize if there are any normal posting procedures that I miss. I am trying to filter a large spreadsheet of data based on products' item codes. Basically the file is a print out of product data and a usage statement below it, so the goal at this point is to be able to filter out the usage statements and different products if possible.

For reference: here are 6 lines of the column I want to filter:

I want to only show data for the 5 ...... 58 rows, excluding the 4 ..... 58 and the usages, &X00. &2P2.

Currently I have a criteria range on a different sheet, and input:

And it seems like the list acts as an "or" operator, it doesn't exclude the 4 NCNDNE 58 row like I would like.

Range(products).AdvancedFilter Action:=xlFilterInPlace, _
     criteriarange:=Worksheets("Criteria").Range(myrange)

Products being the column I'm filtering myrange is the array on the other sheet that is being used as a filter.

Also for what it's worth, I did some reading that advancedfilter accepts "<>" as exclusions, but I have not gotten that to work.

Thanks in advance, I can post more of the code if needed, if it helps. I'm not a coder though, so I'm avoiding posting the whole thing because it'll definitely upset some people with its lack of elegance.

Answer 1

To (for example) include all rows which begin with 5 and end with 58 but do not contain FFF your filter range might look like this (side-by-side is AND'ing the criteria) :

Related: Exclusion Filtering in Excel Advanced Filter