Autofilter Multiple Criteria but with Starts With values instead of Strings

Here’s the setup: I wrote a VBA macro that can be applied to a report that comes out of our database of all the items in the library. The macro looks at the report, filters the call number by a starting number based on the Dewey number we are looking for, copies those results, and then pastes them onto a new sheet (there’s a little more going on, but that’s the gist of it).

For example, for our Geography results, the macro looks at the report, filters the Call Number column by “=91*” to capture all the 910, 911, 912, etc. call numbers. This has worked great until we got to multiple, non-continuous numbers. Our Pure Science results needed to look at call numbers “=50*”, “=52*”, and “=57*”.

My first workaround that worked, until it didn’t (explained below):

Worksheets("LAS Report").Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Worksheets("LAS Report").Range("$A$1").AutoFilter Field:=7, Criteria1:="=50*"
Worksheets("LAS Report").Cells.AutoFilter Field:=6, Criteria1:="<" & Yr5
Selection.CurrentRegion.Select
Selection.Copy

Sheets.Add(after:=Sheets(Sheets.Count)).Name = "PURESCI"
Worksheets("PURESCI").Paste

Worksheets("LAS Report").Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Worksheets("LAS Report").Range("$A$1").AutoFilter Field:=7, Criteria1:="=52*"
Worksheets("LAS Report").Cells.AutoFilter Field:=6, Criteria1:="<" & Yr5
Selection.CurrentRegion.Offset(1, 0).Resize(Selection.CurrentRegion.Rows.Count - 1).Copy
Worksheets("PURESCI").Activate
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lastrow).PasteSpecial

Worksheets("LAS Report").Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Worksheets("LAS Report").Range("$A$1").AutoFilter Field:=7, Criteria1:="=57*"
Worksheets("LAS Report").Cells.AutoFilter Field:=6, Criteria1:="<" & Yr5
Selection.CurrentRegion.Offset(1, 0).Resize(Selection.CurrentRegion.Rows.Count - 1).Copy
Worksheets("PURESCI").Activate
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lastrow).PasteSpecial

Set src = Range("A1").CurrentRegion
Set ws = ActiveSheet
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
xllistobjecthasheaders:=xlYes, tablestylename:="TableStyleMedium4").Name = "PURESCI"

This actually worked for awhile, until there was a report that didn’t have any call numbers starting with those numbers. Instead of just pasting the title row and being done with it like it would do with single criteria results, it would copy and paste the title row for the first criteria “=50*”, go back and try to filter by “=52*” and since it was the second “batch” I normally wouldn’t want it to re- copy & paste the title row again, it moves down one, but there’s no results to move down to, so it would copy all the filtered out rows instead, and then paste the whole report onto the PureSci sheet. Then it would do that a second time, so pasting the whole report again at the bottom.

Here is where someone might have a workaround for the Selection.CurrentRegion.Offset(1, 0).Resize(Selection.CurrentRegion.Rows.Count - 1).Copy line that solves the problem. However, I didn’t know it, so I thought, hmmm, maybe I can use multiple criteria filtering. I need something that will work whether there are results returned or not. If I only had two criteria, it would be so simple, but I have three. Then I looked into an array. I changed three of the call numbers to reflect the 50, 52, and 57 call numbers for testing purposes, so I would know that it was working by finding those three call numbers. I tried:

Worksheets("LAS Report").Range("$A$1").AutoFilter Field:=7, Criteria1:=Array("=50*", "=52*", "=57*"), Operator:=xlFilterValues

However, it came back with no results. Then I changed xlFilterValues to xlOr

Worksheets("LAS Report").Range("$A$1").AutoFilter Field:=7, Criteria1:=Array("=50*", "=52*", "=57*"), Operator:=xlOr

and it worked “better” in that it now comes back with one result because it filters only the last criteria “=57*”. I see this problem solved online when you are looking for strings or a number, but no results if you are trying to filter by “starts with 50 OR starts with 52 OR starts with 57.”

Any suggestions? Thanks!