An Excel Macro to find & copy rows based on a keyword list

April 15, 2012 at 10:39:08
Specs: Windows XP

I've got an Excel document with over 7K entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 50 keywords so doing this using Ctrl+F would take some time. I'm new to Macros but I think that a macro may be the best solution for this task.

The data is in Sheet1 and the particular column that needs to be searched is "K". The contents of column "K" is made up of words and sentences. Sheet2 is where I would like the found results to be copied to. The list of Keywords that need to be searched for are located in Sheet3, starting from Cell A1. Below is a list of the sort of words that need to be searched for:

Tata Motors

Does anyone think they can help me create a macro to carry out this task or know of a better solution for doing it?

Any help would be much appreciated.


See More: An Excel Macro to find & copy rows based on a keyword list

Report •

April 15, 2012 at 11:19:37
Isn't Land Rover 2 words? Do you really want to search for Landrover?

Are any of the keywords found more than once in the same cell in Column K?


"The Jaguar and Land Rover are both members of the Tata Motors family of vehicles."

What do you want to happen if that's the case..copy the line each time any keyword is found (easy) or check to see if the line has already been copied and don't copy it again (tougher).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 15, 2012 at 15:33:36
Hi DerbyDad03,

Thanks for getting back to me.

You are correct, Land Rover should be two words. These are just some examples of the type of words that are in the list.

There may be a handful of cells that contain two or more of the keywords, if it's only a small number of cells then the easier option should be fine.

Do let me know if you need anything else.


Report •

April 15, 2012 at 20:25:05
Try this:

Sub carHunter()
'Determine last row with data in Sheet 3 Column A
  lastSrc_rw = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list of cars
    For Each car In Sheets(3).Range("A1:A" & lastSrc_rw)
'Search for each car, copy row if found
     With Sheets(1).Columns(11)
      Set c = .Find(car, LookIn:=xlValues, lookat:=xlPart)
       If Not c Is Nothing Then
         firstAddress = c.Address
'Find next open row in Sheet 2
          nxtDst_rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy row containing found car
            c.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtDst_rw)
'Search for same car again, stop when no more found
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
       End If
     End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

April 15, 2012 at 23:23:37

Will try this asap and let you know how it goes!

Report •

April 17, 2012 at 04:23:27
Hi DerbyDad03,

Tried the macro and it copied all the correct information over, which is great!

However it also seems to have copied some extra rows that don't consist of the key words. Is there anyway of adding the keywords found in Column K to an additional column (column N) when copying the row over to Sheet2?

Do you think this can be done without too much more work?

Thanks for all your help so far.

Report •

April 17, 2012 at 13:41:25
Obviously, I can't see your workbook from where I'm sitting, but I don't see how it could have copied rows that don't contain one of the keywords in Column K.

All of the code to copy the rows follows an If statement and that code won't be executed unless the .Find instruction actually finds the keyword.

I ran a limited test - 1000 "sentences" in Column K, 884 of which contained one of the keywords from your list.

I ended up with 884 entries in Sheet2, none of which were any of the sentences that did not contain a key word.

If you want the keyword in Column N, find this line:

c.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtDst_rw)

and add this line immediately after it:

Sheets(2).Range("N" & nxtDst_rw) = car

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 7, 2013 at 22:12:04
If I want to search only for the exact word, how do I do it.

Report •

Ask Question