I have the following requirement: I need to be able to filter an excel document based on multiple OR conditions, multilple AND conditions and multiple NOT conditions.
Using the standard filtering in excel I can only include 2 conditions.
So therefore I had the idea to create 3 search fields using VBA: OR, AND , NOT.
For the Or logic I have the following code:
Sub FilterExactAndContains()
Dim ws As Worksheet
Dim filterRange As Range
Dim criteria As String
Dim criteriaArray() As String
Dim i As Integer
Dim visibleRange As Range
Dim firstCriteria As Boolean
' Set the worksheet and the filter range
Set ws = ThisWorkbook.Sheets("Blad1")
Set filterRange = ws.Range("A2:AD" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row) ' Adjusting for columns A to AD
' Ensure the range has data
If filterRange.Rows.Count < 2 Then
MsgBox "No data to filter.", vbExclamation
Exit Sub
End If
' Input criteria
criteria = InputBox("Enter your criteria separated by commas (e.g., Afbraakwerken,Afbreken,vloer)")
' Split the criteria into an array
criteriaArray = Split(criteria, ",")
' Clear any existing filters
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Loop through each criterion
For i = LBound(criteriaArray) To UBound(criteriaArray)
Dim exactMatchCriteria As String
Dim containsCriteria As String
exactMatchCriteria = criteriaArray(i)
containsCriteria = "*" & criteriaArray(i) & "*"
' Apply the filter for exact match
filterRange.AutoFilter Field:=4, Criteria1:=exactMatchCriteria, Operator:=xlOr
' Apply the filter for contains criteria
filterRange.AutoFilter Field:=4, Criteria2:=containsCriteria, Operator:=xlOr
' Combine filters using xlOr to get rows matching any of the criteria
If Not firstCriteria Then
filterRange.AutoFilter Field:=4, Criteria1:=exactMatchCriteria, Operator:=xlOr
firstCriteria = True
Else
filterRange.AutoFilter Field:=4, Criteria2:=exactMatchCriteria, Operator:=xlOr
End If
Next i
' Set visible cells after all filters have been applied
On Error Resume Next
Set visibleRange = filterRange.Resize(filterRange.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
' If visibleRange is Nothing, no rows are visible; otherwise, redefine filterRange
If Not visibleRange Is Nothing Then
Set filterRange = visibleRange.EntireRow
End If
' Remove the filter
ws.AutoFilterMode = False
End Sub
However, it does not work as intended: The results only show the last word that was entered in the search box. So If I would search for example for Apple,Banana I will only see results for banana.
Secondly: I want to be able to search for exact words, but also for wildcards.
Thirdly, the file is extemely slow
Any ideas on how to fix this.
