'Table Autofilter not showing correct results when certain data is entered

I have built a spreadsheet for users to enter certain references and a button to then filter these in the table. The column I am searching has 2 values which I have separated with a pipe | eg 805457976|3700013275. The user should be able to enter the first or second value to get a match. The search values are put into a 1 dimension array, with data type variant/string. I put an asterix * at the start and end of each search value. I then pass this array into the table

Full code as below

Dim wb As Workbook
Dim ws As Worksheet
Dim arRefs As Variant
Dim tbl As ListObject
Dim cell As Range
Dim i As Integer

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(Sheet)
    arRefs = Application.Transpose(Application.Transpose(wb.Names(RangeName).RefersToRange.Value))
    'Remove blanks from Array and enable fuzzy search
    Dim j As Long, jj As Long: jj = 1
    ReDim Arr2(1 To UBound(arRefs))
    For j = 1 To UBound(arRefs)
        If Not IsEmpty(arRefs(j)) Or Trim(arRefs(j)) <> "" Then 'remove blanks
            Arr2(jj) = "*" & arRefs(j) & "*"
            jj = jj + 1
        End If
    Next j
    
    ReDim Preserve Arr2(1 To jj - 1)
    arRefs = Arr2
    
    'Filter Table
    Set tbl = ws.ListObjects(tblName)
    tbl.Range.AutoFilter Field:=16, Criteria1:=arRefs, Operator:=xlFilterValues 

Everything appears to work well, until a value is entered that is not in the table eg.

Filters Correctly

+-----------+------------+----------+----------+----------+
| Search 1  | Search 2   | Search 3 | Search 4 | Search 5 |
+-----------+------------+----------+----------+----------+
| 805457976 | 1018655341 |          |          |          |
+-----------+------------+----------+----------+----------+

Filters Incorrectly (No results at all are shown)

+-----------+------------+------------+----------+----------+
| Search 1  | Search 2   | Search 3   | Search 4 | Search 5 |
+-----------+------------+------------+----------+----------+
| 805457976 | 1018655341 | TestSearch |          |          |
+-----------+------------+------------+----------+----------+

How can I have it so that if a search reference is not found, the table still filters the results that are found?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source