VBA Code --> Search fields: OR, AND, NOT

[LamSar]

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.

[lbendlin]

You posted this in the Power BI/Power Query forum.  Did you mean to post this in a forum dedicated to VBA?

Have you considered running a SQL query against the Excel table?

[LamSar]

I saw other questions of VBA in the Power Query forum, so I also posted my question in this forum.

Would an SQL Query work for advanced filtering requirements (or, and,not)?

Would you perhaps know where I could find documentation to achieve this?

[foodd · Accepted solution]

Please bring your post over to Excel Forum that can be found at https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral .  If you have a Power Query or other Microsoft Fabric question for the community, please do add it here, and many folks will be happy to assist you.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!