WeThotUWasAToad
asked on
Add additional feature to very cool filtering VBA code for Excel
Hello,
For quite some time, I have been using some great VBA code for Excel which was kindly provided by Rory Archibald (rorya) with a slight modification by Saqib Husain, Syed (ssaqibh).
The code is included below but for reference, the original threads are here:
Code to create filtering box at the top of several columns in Excel
Problem with very cool filtering VBA code for Excel
In a nutshell, the code enables quick and easy filtering by simply entering a filter term (in a defined row) and pressing {Enter}. It produces the same filtering results as clicking the drop-down menu and entering the term in the search box. However, the code bypasses the need for the drop-down filter menu and also keeps the search criteria displayed.
For example, suppose the three letters:
try
are entered in the filtering cell at the top of a column. In the code's current form, the filtering results would include each of the following entries (among others):
I am trying to understand.
This is the entry I made.
Try it like this.
But could the code be modified so that if the same 3 letters were preceded by some character, say a backtick (`):
`try
of the above three results, only the 3rd:
Try it like this.
would be included in the results since it actually begins with the letters "try"?
Thanks
For quite some time, I have been using some great VBA code for Excel which was kindly provided by Rory Archibald (rorya) with a slight modification by Saqib Husain, Syed (ssaqibh).
The code is included below but for reference, the original threads are here:
Code to create filtering box at the top of several columns in Excel
Problem with very cool filtering VBA code for Excel
In a nutshell, the code enables quick and easy filtering by simply entering a filter term (in a defined row) and pressing {Enter}. It produces the same filtering results as clicking the drop-down menu and entering the term in the search box. However, the code bypasses the need for the drop-down filter menu and also keeps the search criteria displayed.
Private Sub Worksheet_Change(ByVal Target As Range)
' uses the row above the autofiltered range as a criteria row
' unless the criteria begin with <, > or = the code assumes a 'contains' filter
' so if you need an exact text match, enter ="=text"
' wildcards are allowed
Dim rCriteria As Excel.Range
Dim rData As Excel.Range
Dim rCell As Excel.Range
Dim sCriterion As String
' if there are no filters set up, do nothing
If Me.AutoFilterMode = False Then Exit Sub
Set rData = Me.AutoFilter.Range
' if no criteria row present, don't do anything
If rData.Row = 1 Then Exit Sub
' get criteria range
Set rCriteria = rData.Offset(-1).Resize(1)
' check change was within criteria range
If Not Intersect(Target, rCriteria) Is Nothing Then
For Each rCell In Intersect(Target, rCriteria).Cells
sCriterion = rCell.Value
If Len(sCriterion) = 0 Then
rData.AutoFilter field:=rCell.Column - rData.Column + 1
Else
Select Case Left$(LCase$(sCriterion), 1)
Case ">", "<", "="
' use criteria as entered
Case Else
' append wildcards for 'contains' filter
sCriterion = "*" & sCriterion & "*"
End Select
rData.AutoFilter field:=rCell.Column - rData.Column + 1, Criteria1:=sCriterion
End If
Next rCell
End If
I opened this thread in the hopes that someone can modify the existing code to include an additional feature, namely filtering for the 1st character (which is equivalent to Filter drop-down menu > Text Filters > Begins with…) by including some defined indicator.For example, suppose the three letters:
try
are entered in the filtering cell at the top of a column. In the code's current form, the filtering results would include each of the following entries (among others):
I am trying to understand.
This is the entry I made.
Try it like this.
But could the code be modified so that if the same 3 letters were preceded by some character, say a backtick (`):
`try
of the above three results, only the 3rd:
Try it like this.
would be included in the results since it actually begins with the letters "try"?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.