Add additional feature to very cool filtering VBA code for Excel


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
            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

Open in new window

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:


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 (`):


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"?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Modify the Select Case as follows...

            Select Case Left$(LCase$(sCriterion), 1)
               Case ">", "<", "="
                  ' use criteria as entered
               Case "`"
                  ' remove back tick and append wildcard
                  sCriterion = Mid(sCriterion, 2, Len(sCriterion)) & "*"
               Case Else
                  ' append wildcards for 'contains' filter
                  sCriterion = "*" & sCriterion & "*"
            End Select

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.