an01digital
12/15/2006 11:19:00 AM
Hi Vernal,
Try this...
Sub test()
On Error Resume Next
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim cell As Object
Set S1 = Sheets("Sheet1")
Set S2 = Sheets("Sheet2")
For Each cell In S2.Range("B1:B" & S2.Range("B65536").End(xlUp).Row)
RowInS2 = 0
RowInS2 = S1.Columns("b:b").Find(what:=cell.Value,
lookat:=xlWhole).Row
If RowInS2 = 0 Then
S2.Rows(cell.Row).Copy
S1.Select
Range("C65536").End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste
S2.Select
End If
Next cell
Application.CutCopyMode = False
End Sub
Regards
Ankur Kanchan
www.xlmacros.com
vernalGreens@gmail.com wrote:
> sheet1
>
> column A column B
> R1
> R2 ABC 10
> R3 DEF 12
> R4 LMN
>
> sheet2
> column A column B
> R1
> R2 ABC 9
> R3 XYZ 13
> R4 DEF 11
> R5 PQR 15
>
>
> I've to find out all the column A values of sheet2 that are not in
> column A of sheet1 (in this example XYZ and PQR) and paste the entire
> row of sheet 2 in the next null row under column B of sheet1. Since
> null row under column B of sheet 1 is R4, R4 will be overwritten.
>
> Thus the output will look like
>
> sheet1
> column A column B
> R1
> R2 ABC 10
> R3 DEF 12
> R4 XYZ 13
> R5 PQR 15