Thanks for the code Otto!! It is insightful and it seems to work pretty
well, but like the code that I was using before, the final results are
incorrect. The code returns 265 records (rows) and when I apply several
manual filters I get 127 records. I tried a couple of times; not sure why
the code returns extraneous information. If I have time later I will
investigate these discrepancies further. Anyway, thanks for the help.

Regards,
Ryan---


"Otto Moehrbach" wrote:

Thanks Tom. I missed that. Otto
"Tom Ogilvy" wrote in message
...
Otto,
I don't think you have taken the time to understand the code or have
misunderstood it. I will agree that it is inefficient from a looping
every
cell standpoint, but it works fine and does only one copy and paste after
building a range to all rows that need to be copied. So that should not
be
moved inside the loop and that aspect is very efficient.

--
Regards,
Tom Ogilvy




"Otto Moehrbach" wrote:

Ryan
There are several things wrong with your code. For one thing, it's
inefficient because you are looping through each and every cell in a
rather
large range when what you are really interested in is finding out if any
cell in any row has "sam". That is, you are not interested in what cell
has
"sam", just in what row has "sam". Is this correct?
Your copy/paste commands are both after the For loop. What this means is
that you will paste into A1 of Sheet2 the row of only the last instance
of
"sam". Is it your intention to paste into Sheet2 every row that has
"sam"
in any cell? If so, the copy/paste should be within the For loop.
If I understand what you want to do, your code logic should be something
like:
Set the destination as A1 of Sheet2.
Set the range of Column AR.
Loop through all the cells in that range.
For each cell, Search the entire row (AR to BJ) for "sam" using the Find
command.
Copy/paste the entire row if "sam" is found.
Increment the destination cell one cell down.
Loop to the next cell in Column AR.
Done.
Post back if you need more. HTH Otto

"ryguy7272" wrote in message
...
I wanted to filter several columns for values that match a certain
criteria.
Then I thought a copy/paste routine would work better (there are too
many
columns to use the filter tool). I found this simple cut/paste code on
this
DG a while back and I tried to use it today and it didn't do anything.
Can
someone please tell me why?


Sub a()
Dim Cell As Range
Dim CutRg As Range
For Each Cell In Sheet1.Range("AR1:BJ2000")
If Cell.Value = "sam" Then
If CutRg Is Nothing Then
Set CutRg = Cell.EntireRow
Else
Set CutRg = Union(CutRg, Cell.EntireRow)
End If
End If
Next
If Not CutRg Is Nothing Then
CutRg.Copy Sheet2.Range("A1")
CutRg.Delete
End If
End Sub

There may be multiple instances of the value sought in multiple
columns/rows. I am hoping to copy the entire row if even one instance
of
the
value is found.


Thanks,
Ryan---


--
RyGuy