totn Excel

MS Excel 2003: Search for a value in a column and copy row to new sheet for all matching values

This Excel tutorial explains how to write a macro to search for a value in a column and copy matching rows to a new sheet in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, how do I write a macro that would look for a string in an entire column. If the string is found, it will copy that entire row to a new sheet in that same file?

For Example,

Column E contains all kinds of strings. I'm looking for a cell in this column that says "Mail Box". When the string "Mail Box" is found, the corresponding row should be copied to a new sheet.

Answer: Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

In our spreadsheet, we've created a button on Sheet1 called "Search for String". When the user clicks on this button, a macro called SearchForString will run. This macro will search through the values in Column E looking for "Mail Box". When the "Mail Box" string is found in Column E, the corresponding row will be copied to Sheet2.

Microsoft Excel

When the macro has completed, the above message box will appear.

Microsoft Excel

If you view Sheet2, you can see that only the rows where Column E contains the string "Mail Box" have been copied.

You can press Alt+F11 to view the VBA code.

Macro Code

The macro code looks like this:

Sub SearchForString()

   Dim LSearchRow As Integer
   Dim LCopyToRow As Integer

   On Error GoTo Err_Execute

   'Start search in row 4
   LSearchRow = 4

   'Start copying data to row 2 in Sheet2 (row counter variable)
   LCopyToRow = 2

   While Len(Range("A" & CStr(LSearchRow)).Value) > 0

      'If value in column E = "Mail Box", copy entire row to Sheet2
      If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then

         'Select row in Sheet1 to copy
         Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
         Selection.Copy

         'Paste row into Sheet2 in next row
         Sheets("Sheet2").Select
         Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
         ActiveSheet.Paste

         'Move counter to next row
         LCopyToRow = LCopyToRow + 1

         'Go back to Sheet1 to continue searching
         Sheets("Sheet1").Select

      End If

      LSearchRow = LSearchRow + 1

   Wend

   'Position on cell A3
   Application.CutCopyMode = False
   Range("A3").Select

   MsgBox "All matching data has been copied."

   Exit Sub

Err_Execute:
   MsgBox "An error occurred."

End Sub

TIP: This macro will only find rows where the whole cell matches the search criteria, not text within a cell. In other words, it will not find rows where part of a cell matches the search criteria. If the user wants to search for text matching part of a cell, they can use the InStr function in the code above:

And replace:

If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then

with:

If InStr(1, Range("E" & CStr(LSearchRow)).Value, "Mail Box") > 0 Then