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”.
So here we go.
How to find and extract all matching values
Let’s say you have some data in a range like this.
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.
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.
- We start by asking the user what they want to find and store this in findWhat string variable.
- If the string to find is not empty,
- We clear any previous find results
- We grab the current region for cell B4 (change this to the top-left of your find range)
- We look for findWhat in this range using range.Find method
- As long as Find result is not empty and not same as the first result
- 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.
29 Responses to “Find them and Extract them – VBA Macro”
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...
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
Hi Chadoo Sir,
Really helpful technique.
Thank You,
Bhavani Seetal.
clearFinds sub or function not defined?
Errors out?
Gino
Gah - never mind! I'm a dork - 🙂
I'm a bigger dork. frs not defined. look at variables. its listed as fsr
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
Wondering why I don't have an error.
"Dim fsr As Range"
"Set frs = Range("b4").CurrentRegion"
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.
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.
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?
As I told - seem's to be a typo (exchanged characters) by accident.
Very helpful. Thank you!
Jozef
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?
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
Works great James
How about my other question ?
extracting the whole Line ?
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
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?
@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.
As usual Great Chandoo!!!!!!
Thanks for your valuable State of Art Technique or Master piece. Hoping for some more unique techniques in nearby futures.
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.
Hi ,
Have you checked out these ?
https://www.excel-sql-server.com/
https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/
https://zapier.com/zapbook/excel/sql-server/
Thanks Narayan
Fantastic post !!!
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
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
Hi Chandoo,
I have sent an important message through email.
Request you to reply to it.
Thanks,
Avadhut Nigudkar
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 🙁
It would be a great help if more functionality is added to the code like searching all sheets in the workbook for the given value NOT only in 1 sheet.