Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
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.

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

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:

       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
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.