find value and selet entire row.

[amna77]

Hi all,
In sheet1 I have data in 100 rows. Ok now, if i have "Reject" or "Alert" in Column C, then it should copy that entire row and paste that in sheet2 and so one, find next alert or reject then paste that in sheet2 next empty row.
Any idea how to do that.
Thanks in advance!!!!

[Mark O'Brien]

http://www.mrexcel.com/board/viewtop...c=7878&forum=2

Have a look at that thread to get you started. There's a similar idea there. If you need help adapting it, just repost.

[Adiv]

Put column labels above the first row of your list. Then just set an autofilter on your list. Then filter on the column containing "Reject" or Alert". Then select the rows, copy, activate the target worksheet and paste.

Just my $0.02

[ This Message was edited by: Adiv on 2002-05-16 09:28 ]

[Jay Petrulis]

Hi,

If your range has category headers in row 1, this will do what you require in one shot.

Sub test()

Application.ScreenUpdating = False
With Sheets("Sheet1")
    .UsedRange
    
    .Range("A1").AutoFilter field:=3, _
    Criteria1:="=Alert", Operator:= _
    xlOr, Criteria2:="=Reject"
    
    Cells.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
    
    .Range("A1").AutoFilter
End With
Application.ScreenUpdating = True

End Sub

HTH,
Jay