VBA code required:Compare sheet1 with sheet2 and copy the difference

[mchilapur]

Dear all,
I have two sheets in same excel work book.
Sheet1 has some 5000 rows with new data range from 'A:A to AZ:AZ'.
Same is the case with Sheet2 but 7000 rows of old data.
Few data between both the sheets is common and few data in sheet1 is brand new.

I need a VBA code to compare sheet2 with sheet1 and copy the difference of data from sheet1 to sheet2.

In short, sheet2 must have old data along with new data from sheet1.
I know the range to compare is too high, but plz help me with a fast working code.

PS:I cannot copy entire sheet1 to sheet2 due to some constrains.

Thanks all.

[westconn1]

it is reasonably easy to do this with ADO using an SQL query on the 2 worksheets

try like

Dim cn As Connection, rs As Recordset
Set cn = New Connection
Set rs = New Recordset
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;hdr=no;"""
.Open
End With
Sql = "select * from [sheet1$] as s1 left join [sheet2$]as s2 on s1.f1 = s2.f1 where s2.f1 is null;"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
with thisworkbook.Sheets("sheet2")
.cells(.rows.count,1).end(xlup).offset(1).CopyFromRecordset rs
end with
rs.Close
cn.Closechange any sheet names to suit, you will need to add a reference to ADO or ACE as appropriate to your versions, assumes the code to be in the same workbook as the sheets of data, else change the workbook object