compare records

[Shane]

Hi,

I need to create a macro that compares the contents of sheet1 with sheet2. If the records of sheet1 is matching with sheet2 then remove all those records from sheet2.

Please help.


Regards,
Shane

[Bob Phillips]

All fields matching or just a key field?

[Shane]

Hi Xld,

Thank you to get back to me on this. I am in need fo quick help on the same.

My requirement is if the value of first four columns matches then delete the record from sheets.

Please help

Regards,

Shane

[Shane]

Hi XLD,

Although as per my reuirement i have to match the value of four columns and basis that need to move records to sheet2. However for learning purpose i also want to know how to move records basis any specific cell value.

This is my second question so in case if you have time then please reply this one else answer to my first query will meet my requirement. It wud be a gr8 favor for me.

Thanks for all the help that you can provide.

Regards,

Shane

[Bob Phillips]

[vba]

Sub DeleletData()
Dim LastRow As Long
Dim BaseFormula As String
Dim RunFormula As String
Dim pos As Long
Dim i As Long

With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
BaseFormula = "Match(1," & _
"(Sheet1!B1:B" & LastRow & "=B<row>)*" & _
"(Sheet1!C1:C" & LastRow & "=C<row>)*" & _
"(Sheet1!D1" & LastRow & "=D<row>)*" & _
"(Sheet1!E1:E" & LastRow & "=E<row>),0)"
End With

With Worksheets("Sheet2")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 5 Step -1

RunFormula = Replace$(BaseFormula, "<row>", i)
pos = 0
On Error Resume Next
pos = .Evaluate(RunFormula)
On Error GoTo 0
If pos > 0 Then .Rows(i).Delete
Next i
End With
End Sub
[/vba]

[Shane]

Thank you so much XLD...

You have been of a great help... Thanks a million..




Regards,
Shane

[Bob Phillips]

Shouldn't you be in bed!