PDA

View Full Version : compare records



Shane
05-14-2008, 09:11 PM
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
05-15-2008, 12:31 AM
All fields matching or just a key field?

Shane
05-15-2008, 12:35 AM
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
05-15-2008, 12:40 AM
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
05-15-2008, 04:44 AM
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:D" & 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

Shane
05-15-2008, 05:14 AM
Thank you so much XLD...

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


:hi:

Regards,
Shane

Bob Phillips
05-15-2008, 05:17 AM
Shouldn't you be in bed!