Hello again Stan

Many thanks for this, it's really helpful and does what I hoped.

If you don't mind, can you - or anyone else - help with another problem on
this?

I would like this sub to work without the user selecting the range manually
(ie using the UsedRange property of the active sheet). I have the following
code:

firstRow = ActiveSheet.UsedRange.Cells(1).row
lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1

but it returns a Runtime Error '6' - overflow - which seems to refer to the
second line of the code.

Any pointers most welcomed - as you may have guessed, I'm very new to VBA
programming!

Sue

"stanshoe" wrote:

Once you have identifed the row, you can use the copy method to copy it.
Exactly what you want to copy (Rows(r).Copy, Activecell.EntireRow.Copy, etc.)
depends on how you are looping through the rows.

Here is some code looks for a "String" in the rows selected before the
procedure is run. The macro assumes that the data to be tested is on the
sheet "SourceSheet" and that you want to move it to the sheet
"DestinationSheet".

Sub find_copyRows()
'this procedure assumes the "SourceSheet" is the active worksheet and
'the cells (rows) to be searched have been selected before the macro is
run.

Dim firstrow As Integer
Dim lastrow As Integer

firstrow = ActiveCell.Row
lastrow = firstrow + Selection.Rows.count - 1

For r = firstrow To lastrow
Set c = Rows(r).Find("String")
If Not c Is Nothing Then
Rows(r).Copy
Sheets("DestinationSheet").Activate
Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
(xlPasteAll)
Sheets("SourceSheet").Activate
End If
Next r

End Sub

Stan Shoemaker
Palo Alto, CA

End Sub
"SueJB" wrote:

Hi Stan

Thanks for this - in fact, I have got this loop working and it will identify
any row that contains the string, in any of that row's columns.
Unfortunately it doesn't do much else!

Do you have any suggestions on how to pass the identifier for that row to a
copy routine?

Sue

"stanshoe" wrote:

Sue-

If you focus on rows that contain the string rather than columns, the
problem becomes easier to deal with. You esentially are looking for any row
that has one or more instances of your string. If you set up your procedure
loop through the data to seach for the string on one row at a time, you can
quickly ascertain whether the row needs to be copied or not. If it contains
the string, copy it and move to the next row. If it doesn't contain the
string, simply move on to the next row.

I hope this helps

Stan Shoemaker
Palo Alto, CA


"SueJB" wrote:

I have a multi-column worksheet that I need to search for a particular
string, which could appear in any column (and possibly in more than one
column) in a row. When I find that string, I want to copy the entire row to
the next empty row on a separate worksheet.

However, if the value appears more than once in a row, I only want one copy.

I have a number of have-way-there solutions but nothing is doing the job
properly. Has anyone any suggestions please?

Many thanks!