VBA SOURCE
CODE BOOK
How To Filter Excel Table
Data Just By Entering Text
In The Header
DOWNLOAD VIEW
APPLICATION TRAINING
by: Randy Austin
ABOUT THE AUTHOR
A two-time Microsoft MVP & lifetime Excel enthusiast, Randy
Austin founded Excel For Freelancers in 2017. Excel For
Freelancers quickly became the most prominent resource Excel
for developers to learn how to turn their passion for Excel into
profits by building & selling their own excel-based applications
for passive & recurring income.
With nearly 300,000 YouTube subscribers, 14,000,000 video
views, 200+ comprehensive training videos, and a thriving 40,000
member Facebook community, Excel For Freelancers has
positioned itself as the #1 Excel developers resource in the world.
Get free content, training, and downloads just by clicking any of the free
resources below:
WEBSITE YOUTUBE FACEBOOK TWITTER
INSTAGRAM TELEGRAM RUMBLE
OUR COURSES &
PRODUCTS
This comprehensive program will take you through
a 12-phase process that will turn your enthusiasm
for Excel into passive income.
Click here to learn more
16 hour masterclass that will teach you the tips,
tricks and techniques on how to create a dynamic
single-click dashboard, and a ton more
Click here to learn more
Incredible Package of 175 of my BEST Applications
into a SINGLE ZIP File which also includes the "175
Workbook Library".
Click here to learn more
With 1000 live links, continuously updating content,
sort-able and filterable items, you will always have
exactly what you need, when you need it.
Click here to learn more
Table of Contents
Projects ..............................................................................................................................................................................................2
VBAProject .....................................................................................................................................................................................2
Documents ..................................................................................................................................................................................2
Sheet1......................................................................................................................................................................................2
Worksheet_BeforeDoubleClick [Sub ] ...................................................................................................................................2
Worksheet_Change [Sub ] ....................................................................................................................................................2
Sheet2......................................................................................................................................................................................4
(Declarations)........................................................................................................................................................................4
Sheet3......................................................................................................................................................................................6
(Declarations)........................................................................................................................................................................6
ThisWorkbook ..........................................................................................................................................................................8
(Declarations)........................................................................................................................................................................8
Modules .....................................................................................................................................................................................10
FilteringModule .......................................................................................................................................................................10
(Declarations)......................................................................................................................................................................10
ClearFilt [Sub ] ....................................................................................................................................................................10
TableFilt [Sub ] ....................................................................................................................................................................10
1 of 11
T
Sheet1 Table_Header_Filtering.xlsm
1 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
2 If Not Intersect(Target, Range("E7:E1000" )) Is Nothing And [Link] <> Empty Then
3 Range("E5" ).Value = Range("E" & [Link]).Value
4 End If
5 End Sub
6
7 Private Sub Worksheet_Change(ByVal Target As Range)
8
9 If Not Intersect(Target, Range("E5:G5,I5" )) Is Nothing And Range("B4" ).Value = False
Then
10 TableFilt
11 End If
12
13 End Sub
14
2 of 11
Index
C
Cancel, 2
E
Empty, 2
I
Intersect, 2
R
Range, 2
Row, 2
T
TableFilt, 2
Target, 2
V
Value, 2
W
Worksheet_BeforeDoubleClick, 2
Worksheet_Change, 2
3 of 11
Sheet2 Table_Header_Filtering.xlsm
1 Option Explicit
2
4 of 11
Index
E
Explicit, 4
5 of 11
Sheet3 Table_Header_Filtering.xlsm
1 Option Explicit
2
6 of 11
Index
E
Explicit, 6
7 of 11
ThisWorkbook Table_Header_Filtering.xlsm
1 Option Explicit
2
8 of 11
Index
E
Explicit, 8
9 of 11
T
FilteringModule Table_Header_Filtering.xlsm
1 Option Explicit
2
3 Sub ClearFilt()
4 With Sheet1
5 .Range("B4" ).Value = True
6 .AutoFilterMode = False
7 .Range("E5" ).Value = "Enter Name"
8 .Range("F5" ).Value = "From Date"
9 .Range("G5" ).Value = "To Date"
10 .Range("I5" ).Value = "Enter Address"
11 .Range("B4" ).Value = False
12 End With
13 End Sub
14
15 Sub TableFilt()
16 Dim ContName As String
17 Dim ContAdd As String
18 Dim ToDate As Date
19 Dim FrDate As Date
20 Dim LastRow As Long
21 With Sheet1
22 LastRow = .Range("E99999" ).End(xlUp).Row
23 If LastRow < 6 Then LastRow = 6
24 If .Range("E5" ).Value = "Enter Name" Then ContName = Empty Else: ContName = .Range(
"E5" ).Value 'Contact Name
25 If .Range("F5" ).Value = "From Date" Then FrDate = "1/1/1900" Else: FrDate = .Range(
"F5" ).Value 'From Date
26 If .Range("G5" ).Value = "To Date" Then ToDate = "1/1/2030" Else: ToDate = .Range(
"G5" ).Value 'To Date
27 If .Range("I5" ).Value = "Enter Address" Then ContAdd = Empty Else: ContAdd = .Range(
"I5" ).Value 'Address
28 .Range("E6:O" & LastRow).Select
29 [Link]
30 With .Range("E6:O" & LastRow)
31 If ContName <> Empty Then .AutoFilter Field:=1, Criteria1:="=*" & ContName & "*"
32 .AutoFilter Field:=2, Criteria1:=">=" & FrDate, Operator:=xlAnd, Criteria2:="<=" &
ToDate
33 If ContAdd <> Empty Then .AutoFilter Field:=5, Criteria1:="=*" & ContAdd & "*"
34 End With
35 .Range("6:6" ).[Link] = True
36 End With
37 End Sub
10 of 11
Index
A
AutoFilter, 10
AutoFilterMode, 10
C
ClearFilt, 10
ContAdd, 10
ContName, 10
Criteria1, 10
Criteria2, 10
E
Empty, 10
EntireRow, 10
Explicit, 10
F
Field, 10
FrDate, 10
H
Hidden, 10
L
LastRow, 10
O
Operator, 10
R
Range, 10
Row, 10
S
Selection, 10
Sheet1, 10
T
TableFilt, 10
ToDate, 10
V
Value, 10
X
xlAnd, 10
xlUp, 10
11 of 11
Thank You!
This source code was created and made available
to help you gain a better understanding of how
VBA is used to create amazing Excel-based
applications.
Thank you so much for your continued shares,
likes and support. It really helps.