Filter Excel Tables Automatically Using a Simple VBA Macro

This simple macro provides a swift way to search for text entries in a large data table, without having to click on the filter icon, scan the entries manually, and check and un-check multiple boxes. You can program the macro to do either full or partial searches, and the macro can be programmed to be triggered either by the click of a button or on change of the value of the cell earmarked as the search cell.

Full search example:

Partial search example:

The macro for full search is as follows:

Sub filterTblFull()

Dim KeyID As String
Dim col As Integer

KeyID = Range("A1").Text
ActiveSheet.ListObjects("Table2").AutoFilter.ShowAllData

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=KeyID

End Sub

while the one for the partial search is below:

Sub filterTblPartial()

Dim KeyID As String
Dim col As Integer

KeyID = "" & Range("C2").Text & ""
col = Range("C4").Value

ActiveSheet.ListObjects("Table2").AutoFilter.ShowAllData
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=KeyID

End Sub

Adjust the ListObject (from "Table2") to the name of your table in Excel. You can find this name by selecting any cell within the table range, and clicking on the Table Design tab in the ribbon (see below):

The Range.AutoFilter Field references the column to be searched, change 1 to the number of the column you want the search to focus on).

This line in the code ("ActiveSheet.ListObjects("Table2").AutoFilter.ShowAllData") programs the table to clear all existing filters before the filter is executed.

Both the full search and partial search macros above can be executed by assigning them to a standard Microsoft Office Shape (Insert > Shape) or a Form control (Developer > Insert > Form Control > Form button).

However, if you wanted this code to be executed once the search cell receives a new value, program them as sheet macros. You can do this in Visual Basic Editor, by selecting the sheet object of the active sheet and entering the code, rather than entering the code in a new module), and using the code structure below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyID As String

KeyID = Range("B1").Text

If Not Application.Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then

ActiveSheet.ListObjects("Table2").AutoFilter.ShowAllData
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=col, Criteria1:=KeyID

End Sub