CODEWITHSUNDEEP
excelvbaexcel-formula
Hashmat Habibzadah
Hashmat Habibzadah

Reputation: 45

Filter Table by Search Fields (Excel)

I have a table in excel, and I want my users to be able to filter on it based on a few search fields.

I want them to be able to search part of the field (i.e. McD) and I want them to be able to fill in 1, 2 or 3 of the criteria.

I found the code below online but it is limited and doesn't seem to work

   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

Please see the image below for illustration

enter image description here

Upvotes: 0

Views: 79

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Test the next code, please. But before that, please arrange your filter criteria transposed.

I mean to have your fields name on first row (from A1 to C1), and criteria (McDonalds, for instance) on second row (from A2 to C2):

Sub testFilterAutomation()
  Dim sh As Worksheet, lastRow As Long
  Set sh = ActiveSheet ' use here your sheet
  lastRow = sh.Range("A" & Rows.Count).End(xlUp).row
    sh.Range("A5:G" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=sh.Range("A1:C2")
End Sub

If you want to automate the process, copy, please the next code in the sheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.address(0, 0) = "E1" Then
        If UCase(Target.Value) = "X" Or UCase(Target.Value) = "Y" Then
            Dim lastRow As Long
            lastRow = Range("A" & Rows.Count).End(xlUp).row
              Range("A5:G" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:C2")
        End If
    End If
End Sub

If you change the E1 value in "X" or "Y" the code will be triggered. You can use a Data Validation list (with only x and y) and changing from one to the other, the code will be triggered.

Upvotes: 1

Related Questions