Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using VBA to filter Excel column based on multiple keywords

Posted on 2011-09-18
5
Medium Priority
?
678 Views
Last Modified: 2012-05-12
Using VBA I need a function to filter an Excel spreadsheet based on a string of keywords. The rows need to contain (all) the keywords, but not necessarily in the same order as listed.

There is a previous question/answer that partially addresses this, but it returns the rows that contain (any) of the keywords, not all of them.

I'm searching only column 'B'

Below is the reference to the previous entry for reference.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21740621.html?sfQueryTermInfo=1+10+30+filter+keyword+multipl 
'Credit Webtubbs at above solution

Sub ShowMatches()
     Dim r As Long
     Dim LastRow As Long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim i As Long
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = [D65536].End(xlUp).Row + 1
     For r = LastRow To 2 Step -1
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "D"), arr(i)) > 0 Then
                    HideRow = False
               End If
          Next
          Rows(r).Hidden = HideRow
          HideRow = True
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:gedwardnelson
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36557862
I made the requested changes to your code but without any data I have not tested it.

I noted that the code you attached was searching column d not B so I have changed that as well.

If the code below does not work could you please provide some sample data to test against

Michael
Sub ShowMatches()
     Dim r As Long, LastRow As Long, i as Long, count as long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = Range("B" & rows.count).End(xlUp).row + 1
     For r = LastRow To 2 Step -1
          count = 0
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "B"), arr(i)) > 0 Then count = count + 1
          Next
          if count = UBound(arr) then HideRow = False
          Rows(r).Hidden = HideRow
          HideRow = True
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:gedwardnelson
ID: 36557953
Michael,

Thanks for the quick response. It doesn't appear to search for the second keyword. I have attached a small example of the file.

A couple of examples of what I might be searching for is
2'' X 1'' PVDF REDUCER
or
2'' 316L SS O2 CLEAN TUBING

I might enter something like 2;PVDF and this would list all rows with a "2" AND "PVDF." Or I might enter PVDF;Reducer and get all rows with "PVDF" and "REDUCER", or I might enter 2;SS;Tubing. Or I might just enter a single keyword of "PVDF." Each of these entries might result in more than each of the items listed above but it would narrow down the list significantly, or could actually result in the exact item.

I forgot to mention that this should not be case-sensitive because there hasn't been any consistency in how items were entered.

George
FilterTest.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1600 total points
ID: 36559164
Small reversal of the original logic:
Sub ShowMatchesOrig()
   Dim r                 As Long
   Dim LastRow           As Long
   Dim SearchCriteria    As String
   Dim arr()             As String
   Dim i                 As Long
   Dim HideRow           As Boolean

   SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                             vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
   If SearchCriteria = "" Then Exit Sub
   Application.ScreenUpdating = False
   arr = Split(SearchCriteria, ";")
   LastRow = Cells(Rows.count, "B").End(xlUp).Row
   For r = LastRow To 2 Step -1
      HideRow = False
      For i = LBound(arr) To UBound(arr)
'         If InStr(1, Cells(r, "B"), arr(i), vbTextCompare) = 0 Then
         If InStr(1, Cells(r, "B"), arr(i)) = 0 Then
            HideRow = True
            Exit For
         End If
      Next i
      Rows(r).Hidden = HideRow
      HideRow = True
   Next
   Application.ScreenUpdating = True
End Sub

Open in new window


Note this is case sensitive. If you don't want that, uncomment the commented line and comment the one below it.
0
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 400 total points
ID: 36559413
George

Rorya has nailed it and deserves the points, I just want to see where I went wrong and thought I would post the corrected code

Michael
Sub ShowMatches()
     Dim r As Long, LastRow As Long, i As Long, count As Long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = Range("B" & Rows.count).End(xlUp).Row
     For r = LastRow To 2 Step -1
          count = 0
          HideRow = True
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "B").Value, arr(i), vbTextCompare) > 0 Then count = count + 1
          Next
          If count = UBound(arr) + 1 Then HideRow = False
          Rows(r).Hidden = HideRow
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Closing Comment

by:gedwardnelson
ID: 36561249
Thanks Rorya. That worked exactly as I needed. I made the change you suggested to make it non-case-sensitive.

Michael thanks for your help too. I awarded points to Rorya as you suggested, but I wanted you and anyone else that may be looking at this that your final solution worked too.

George
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question