Find them and Extract them – VBA Macro

Posted on February 10th, 2017 in VBA Macros - 28 comments

I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said, “ministry of MAGIC!!!”). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”

I explained him how to do this while chewing mouthfuls of rice & veggies.

But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.

find-and-extract-smith-v4

So here we go.

How to find and extract all matching values

Let’s say you have some data in a range like this.

find-and-extract-data

And you want to find all cells with comp in them. If the values are all in one column, you could use auto-filter to quickly filter cells with comp in them and copy paste them to a target range.You can even automate the steps a bit with advanced filter

But what if the data can be in any column?

We can use Find (Ctrl+F) to find the values and click on “Find all” to see all results in the find box. But to extract them, we must take the red pill and escape the limitations of Excel to enter in to the exciting world of VBA.

Here is a quick demo of what our find and extract macro does.

find-extract-all-macro-demo

Here is the code:


Sub findAll()
    Dim findWhat As String, address As String
    Dim fsr As Range, rs As Range, fCount As Long
    
    findWhat = InputBox("Enter what you want to find?", "Find what...")
    
    If Len(findWhat) > 0 Then
        clearFinds
        Set frs = Range("b4").CurrentRegion
        Set rs = frs.Find(What:=findWhat)
        If Not rs Is Nothing Then
            address = rs.address
            Do
                Range("I5").Offset(fCount).Value = rs.Value
                Range("J5").Offset(fCount).Value = rs.address
                Set rs = frs.FindNext(rs)
                fCount = fCount + 1
            Loop While Not rs Is Nothing And rs.address <> address
        End If
    End If
End Sub

How does it work?

The code is inspired from Bill Jelen’s excellent example on Find method on MSDN.

The logic goes like this.

  1. We start by asking the user what they want to find and store this in findWhat string variable.
  2. If the string to find is not empty,
  3. We clear any previous find results
  4. We grab the current region for cell B4 (change this to the top-left of your find range)
  5. We look for findWhat in this range using range.Find method
  6. As long as Find result is not empty and not same as the first result
    1. We copy the value & address to I5 (change this to target range as per your workbook setup)

Download the Find and Extract workbook

Click here to download the example workbook. Play with the macro to learn its inner workings.

The rabbit hole is deep, don’t stop just here…

If you enjoyed this little macro, you are going to love VBA. Check out our free starter tutorial or extensive VBA section for more.

How would you find and extract results?

I thought the Find method approach would be slow, but I am surprised to see that on a medium sized dataset (12000 values), the macro produced results almost instantly. So I would be using it more often to iterate thru a range to find a value.

What about you? Do you have such problems at work? Do you use VBA to solve them or just ask colleagues during lunch break and hope for a miracle? Please share your approach in the comments.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

28 Responses to “Find them and Extract them – VBA Macro”

  1. scipion says:

    Interesting.. hope NZ inspire you more & more and that you start enjoying rugby and All blacks legends 🙂
    On the same topic, one other common need is to extract another sheet all the rows for which at least one of the columns contains the searched value, but keeping the format of initial table (same number of columns).
    sure we can use the VBA provided a starting point...

  2. Hi Chandoo,

    This is a very good topic for your audience. I have the need to select and extract data all the time. As a result, I created a VBA function that is somewhat similar to yours.

    https://sites.google.com/site/beyondexcel/project-updates/selrows

    Hope you are liking NZ. Please say hi to Jeff for me. Best wishes - Craig

  3. Bhavani Seetal says:

    Hi Chadoo Sir,

    Really helpful technique.

    Thank You,
    Bhavani Seetal.

  4. Gino says:

    clearFinds sub or function not defined?

    Errors out?

    Gino

  5. Gary says:

    Love this! I wonder how you would add one little extra piece. I would like to add what was searched in a little box, say just below the "Find and Extract" arrow, maybe in cell G9. I would probably put a little title that says something like "Search Results For:," or something like that.

    Thank you!

    Gary

  6. jomili says:

    Wondering why I don't have an error.

    "Dim fsr As Range"
    "Set frs = Range("b4").CurrentRegion"

    • NARAYAN says:

      Hi ,

      The VBA variable fsr has been declared as a variable of type Range.

      The VBA variable frs has not been declared , and hence Excel assigns it the type Variant.

      If you see the Microsoft help on the Variant type , it says this :

      A Variant is a special data type that can contain any kind of data except fixed-length String data. You can determine how the data in a Variant is treated using the VarType function or TypeName function.

      Hence , once the statement :

      Set frs = Range("b4").CurrentRegion

      is executed , Excel assigns the data type Range to the VBA variable frs. You can check this out , by stepping through the code , and once this line has been executed , entering the following in the Immediate window :

      ?typename(frs)

      The only error will therefore be if you have Option Explicit in your module , in which case , since frs is not declared , Excel will generate an error before the code is executed.

      • Hugo says:

        Dim fsr As Range
        Set frs = Range("b4").CurrentRegion

        This is a typo. fsr and frs.
        Dim frs As Range
        will do it.

        I'd prefer to have an Option Explicit as first line.
        Then a warning would appear; so you'd find the typo.

      • Daryl says:

        Makes sense. I virtually always use Option Explicit so didn't event consider it might be an undeclared variable. On a related issue, "fsr" is declared but not used in the macro. Or am I missing something?

  7. Jozef Melíchar says:

    Very helpful. Thank you!
    Jozef

  8. Jim says:

    Would be great if the macro could create the found item as a hyperlink back to the list
    also if we could extract the row the item is found in?

    • James says:

      You can use the hyperlink function to have the cell contain hyperlinks to the location instead of (or in addition to if you keep the range("j5") line) listing the location the the right
      Add at top
      Dim sSheet as string
      sSheet = activesheet().name

      then replace
      Range("I5").Offset(fCount).Value = rs.Value
      Range("J5").Offset(fCount).Value = rs.address
      with
      Range("I5").Offset(fCount).formula = "=HYPERLINK(""#'" & sSheet &"'!" & rs.address & """,""" & rs.Value & """)"

      in this example if sheet was "sheet1" then I5 would end up being
      =HYPERLINK("#'Sheet1'!$B$29","Jerry Walker")
      which when you clicked on the cell would go to the value in the workbook

      • Jim says:

        Works great James

        How about my other question ?
        extracting the whole Line ?

        • James says:

          add
          For Each rCol In frs.Columns
          Range("I5").Offset(fCount, 2 + rCol.Column - frs.Column).Value = frs(rs.Row - frs.Row + 1, rCol.Column - frs.Column + 1)
          Next rCol

          Gary entire code (this does include the line of the table as, but you can just subtract out the code above to remove that)

          Option Explicit
          Sub findAll()
          Dim findWhat As String, address As String
          Dim frs As Range, rs As Range, fCount As Long, rCol As Variant
          Dim sSheet As String
          sSheet = ActiveSheet().Name

          findWhat = InputBox("Enter what you want to find?", "Find what...")

          If Len(findWhat) > 0 Then
          clearFinds
          Set frs = Range("b4").CurrentRegion
          Set rs = frs.Find(What:=findWhat)
          If Not rs Is Nothing Then
          address = rs.address
          Do
          Range("I5").Offset(fCount).Formula = "=HYPERLINK(""#'" & sSheet & "'!" & rs.address & """,""" & rs.Value & """)"
          Range("I5").Offset(fCount, 1).Value = rs.address
          For Each rCol In frs.Columns
          Range("I5").Offset(fCount, 2 + rCol.Column - frs.Column).Value = frs(rs.Row - frs.Row + 1, rCol.Column - frs.Column + 1)
          Next rCol
          Set rs = frs.FindNext(rs)
          fCount = fCount + 1
          Loop While Not rs Is Nothing And rs.address address
          End If
          End If
          End Sub
          Sub clearFinds()
          Range(Range("I5:J5"), Range("I5:J5").End(xlDown)).Clear
          End Sub

  9. Idan says:

    I think this code is useful and can be applied to all kinda purposes, but for the example you've shown wouldn't it be simpler to create an auxilary column that concatenates all other columns and then apply the filter on that column?

    • Chandoo says:

      @Idan... if it is a one off thing, I would use this approach. But if you need to extract matching data everyday for various datasets, then VBA is the way to go.

  10. Amarnath says:

    As usual Great Chandoo!!!!!!
    Thanks for your valuable State of Art Technique or Master piece. Hoping for some more unique techniques in nearby futures.

  11. Justin says:

    Chandoo you had me at SQL servers!!! I have been searching for a page just like this that is for SQL server but have failed to find one.

    I know my Excel skills still need help but the business is taking me the way of the SQL so if anyone knows of a site that can help that would be great.

    Back on topic this is another great piece of work.

  12. William says:

    Fantastic post !!!

  13. Gary says:

    James, I like your hyperlink addition, but am not quite sure I understand it. It looks like it is going to look for "Jerry Walker" when you run it. Am I misunderstanding it? I would want it to pull the value that I enter as the previous macro does. Also, would you mind providing the entire code, with your addition and change, rather than just the addition and change? I'm really new to VBA and am having a bit of trouble putting your code into the original code.

    Thank you!

    Gary

  14. Jordan says:

    Chandoo,

    Thank you so much. I found this very helpful. Made some edits to make it more universal:
    1) Populate in a userform so I can use a RefEdit box to define the range to search.
    2) Borrowed a portion of Jame's code to copy/paste into a new sheet.
    3) Added a pop-up if range or search value is not complete

    See code below if anyone is interested. Rangeselect is the RefEdit and TextFind is a textbox.

    Private Sub OK_Click()
    Dim findWhat As String, address As String
    Dim fsr As Range, rs As Range, fCount As Long, rCol As Variant

    findWhat = TextFind.Value

    If Len(findWhat) = 0 Or Len(RangeSelect.Value) = 0 Then
    MsgBox ("Please enter text to find or range")
    Exit Sub
    End If
    Application.ScreenUpdating = False
    Set frs = Range(RangeSelect.Value)
    Set rs = frs.Find(What:=findWhat)
    If Not rs Is Nothing Then
    address = rs.address
    Sheets.Add After:=Sheets(Sheets.Count)
    Do
    For Each rCol In frs.Columns
    Range("A1").Offset(fCount, rCol.Column - frs.Column).Value = frs(rs.Row - frs.Row + 1, rCol.Column - frs.Column + 1)
    Next rCol
    Set rs = frs.FindNext(rs)
    fCount = fCount + 1
    Loop While Not rs Is Nothing And rs.address address
    Application.ScreenUpdating = True
    End If
    Unload Me
    End Sub

  15. Avadhut says:

    Hi Chandoo,

    I have sent an important message through email.

    Request you to reply to it.

    Thanks,
    Avadhut Nigudkar

  16. umar says:

    Please can you show me books tutorial or anything where i can learn about VBA
    I do not have a clue and really need it 🙁

Leave a Reply