Locating a value anywhere in a row and copying that row to new she

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Sue-

There is another property called "CurrentRegion" that I find most useful if
the data set has no enitirely blank rows or columns in the data set.
CurrentRegion is the range bounded by any combination of blank rows and blank
columns. If you data is in rows 1 to 100 starting in Column A and row 51 is
blank, Range("A1").CurrentRegion.Select will return a range of cells from Row
1 to Row 50. If Row 51 is not blank, you will get all of the rows between 1
and 100.

Assuming your data starts in Cell A1, you could use the following code to
automatically select your range:

Range("A1").CurrentRegion.Select

firstRow = ActiveCell.row
lastRow = firstRow + Selection.Rows.count - 1

Alternatively, if you data allways starts in row 1, but there are blank rows
in the middle, you could establish the last row by selecting a cell at the
bottom of the worksheet and using the "End(xlUp)" up method to locate the
last row. In this situation you could use code like:

firstRow = 1
lastRow = Range("A60000").End(xlUp).Row

Stan Shoemaker
Palo Alto, CA

 

[Guest]

Stan

Very many thanks - that should solve my problem beautifully and has taught
me a lot too.

Best wishes
Sue