Search for a word in a worksheet and return row Closed

Question

Hi, 

Hoping to get some help. I have a spreadsheet full of data in Sheet 1 from A1:AG159382. I would like to use a separate worksheet to search for a word within all the cells in Column AG of Sheet 1 and when that word is found I want to return everything that in that row. I want to return no value if my word is not in column AG.

vcoolio · Answer

Hello TinaMarie,

The following code, using autofilter should do the task for you:-


Sub TransferData()

Application.ScreenUpdating = False

Dim Srch As String

Srch = InputBox("Please enter the word to search.")
If Srch = vbNullString Then Exit Sub

Sheet1.Range("AG1", Sheet1.Range("AG" & Rows.Count).End(xlUp)).AutoFilter 1, Srch
         Sheet1.Range("A2", Sheet1.Range("AG" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
         Sheet1.[AG1].AutoFilter

Sheet2.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code uses an Input Box in which you can enter any word to search in Column AG. If any matches are found in Column AG in the sheet1 data set, then the whole relevant row of data is transferred to sheet2.

The code also assumes that there are headings in sheet1, row1.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/sinzs0xxd08ld28/Tina16Marie%28Autofilter%20large%20dataset%29.xlsm?dl=0

Click on the button in sheet2 to open the Input Box and then type in a criteria from sheet1 Column AG (for the sake of the exercise, I have used "Dog", "Cat", "Mouse", "Lion" and "Tiger" as the criteria to search for in Column AG). The Input Box is case sensitive, so type the criteria into the Input Box exactly as it is spelt in Column AG.

My test work book is based on 10K rows of data but I have tested the code on 50K rows and the code still executes in less than a second.

Do you need the "used" data cleared from sheet1 once transferred?

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello TinaMarie,

 Is there a way to perform the search without typing the word that I need to manually search. I would like for it to automatically perform the search once I paste the data into Sheet 1. 

Is there only one word to be used as the criteria in Column AG?

If so, a Worksheet_Change event could do as you ask. If not, then its back to the Input Box or a search box at least.

Also, my data in AG will have other words in it. For example, I want to be able to search for the word (Dog) in a cell that contain the words (Dog, Cat, Mouse, Lion and Tiger).

Do you mean that there are multiple words in a single cell in Column AG or are you just referring to different words in different cells in Column AG?

To clear things up, it would be good if you could upload a sample of your work book (just a few rows of data, not the whole lot) so we can determine how to go about a solution for you. You can upload a sample to a free file sharing site such as DropBox (as I have done above) and then post the link to your file back here. Be careful with any sensitive data.

Cheerio,
vcoolio.

Search for a word in a worksheet and return row

2 responses

Subscribe To Our Newsletter!