Is there a way to run Autofilter to more than one column simultaneously in Excel VBA?

Question

I have a cell designated as a Search Box for user entry (called 'UserSearch') and need to be able to use this input to filter multiple columns at the same time. For example, if the user searched for 'Apple', I need the VBA code to filter out all rows where that word appears, even if it appeared in another column. I am currently stuck on only being able to filter out one column at a time but this input may also appear in another column but the row won't be filtered because it may have gotten filtered out by the column before it.

My current code is below is:

Sub search()
    With ActiveSheet.Range("$a$4:$j$30")
       .AutoFilter Field:=1, Criteria1:="=*" & Range("UserSearch") & "*", Operator:=xlOr
       .AutoFilter Field:=2, Criteria1:="=*" & Range("UserSearch") & "*", Operator:=xlOr
       .AutoFilter Field:=3, Criteria1:="=*" & Range("UserSearch") & "*"
    End With
End Sub

As you can see, my goal is to be able to run autofilter on all 3 fields simultaneously (essentially treating the 3 columns as just one) but the code above contradicts each other and no rows are returned. Anyone have any idea by using autofilter?

First of all, if a row has multiple apples, you would filter it out anyway right, why do you need to filter simultaneously? Second of all, you can't technically filter simultaneously in Excel. Anyways, what i would do was to locate the rows to filter out first, then filter them afterwards. :-) — Daghan, Feb 26 '20 at 14:09
Can you give an example of the data and how you want it filtered? The `AutoFilter` is doing exactly what you are telling it to do, so you may need a different approach to achieve your desired result. — PeterT, Feb 26 '20 at 14:09
You cannot use `.AutoFilter` for this but yes using a small vba code you can achieve what you want — Siddharth Rout, Feb 26 '20 at 14:11

score 3 · Accepted Answer · answered Feb 26 '20 at 14:27

You cannot use .AutoFilter for this but yes using a small vba code you can achieve what you want

Let's say your worksheet looks like this

Paste this code in a module

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rngHide As Range
    Dim FoundIt As Long, i As Long, lRow As Long
    Dim SearchString As String

    '~~> Your search string
    SearchString = "Apple"

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    '~~> Find the last row
    ' https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba
    lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    '~~> Loop through 4 to last row to find the search string
    For i = 4 To lRow
        On Error Resume Next
        FoundIt = Application.WorksheetFunction.Match(SearchString, ws.Rows(i), 0)
        On Error GoTo 0

        '~~> Create a range which needs to be hidden
        If FoundIt = 0 Then
            If rngHide Is Nothing Then
                Set rngHide = ws.Rows(i)
            Else
                Set rngHide = Union(rngHide, ws.Rows(i))
            End If
        End If
        FoundIt = 0
    Next i

    '~~> Hide it if applicable
    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
End Sub

I have commented the code so you should not have a problem understanding it. but if you do then simply ask.

In Action

Thanks Siddharth, it works perfectly. However in the end I created another column and used concat() function to merge the columns of interest and re-pasted as values which then allowed me to use one simple line of code with .autofilter to solve the problem. Out of curiosity, how would you code a 'Clear' button to undo the code you wrote above? — AW25, Feb 27 '20 at 09:11
Using a concat is a bad idea. It will give you false positives. For example One cell has the word appleton or any other word which has the text "apple" in it then it will give you a wrong result. The code that I gave above will work with exact matches — Siddharth Rout, Feb 27 '20 at 09:27

score 0 · Answer 2 · answered Feb 26 '20 at 17:59

These two macros are more basic, but accomplish the same task as Sid's answer...

The first macro loops through the range and checks the first three cells in the current row for the search text, if found in any of the cells, it will loop to the next row. If no cells contain the search text, the row will be hidden

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") 'Define your worksheet
Dim UserSearch As String: UserSearch = ws.Range("A2").Value 'Assign the range for the user entry, change as needed

    For Each cel In ws.Range("A4", ws.Cells(ws.Rows.Count, 1).End(xlUp)) 'Loop through the range
        'Using (= and Or) test if any of the first three cells in the current row contain the search text
        If cel.Value = UserSearch Or cel.Offset(, 1).Value = UserSearch Or cel.Offset(, 2).Value = UserSearch Then
            'If the search text is found in any of the cells then loop to the next row
        Else
            'If the search text is not in any of the cells then hide the row
            cel.EntireRow.Hidden = True
        End If
    Next cel

The second macro loops through the range and checks the first three cells in the current row for the search text, if not found, the row will be hidden

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") 'Define your worksheet
Dim UserSearch As String: UserSearch = ws.Range("A2").Value 'Assign the range for the user entry, change the range as needed

    For Each cel In ws.Range("A4", ws.Cells(ws.Rows.Count, 1).End(xlUp)) 'Loop through the range
        'Using (<> and And) test the first three cells in the current row
        If cel.Value <> UserSearch And cel.Offset(, 1).Value <> UserSearch And cel.Offset(, 2).Value <> UserSearch Then
            'If the search text is not found hide the current row
            cel.EntireRow.Hidden = True
        End If
    Next cel

Is there a way to run Autofilter to more than one column simultaneously in Excel VBA?

2 Answers2