'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 |
|---|
