Hi

This compare column A in Book1.xls to Column A in Book2.xls
and deletes the entire row in book 1 for any duplicates found. It should be
fairly intuative on how to change workbook names and columns.

Alt + F11 to open VB editor. Right click this workbook and insert module and
paste this in

Sub deleteit()
Dim MyRange, MyRange1, Bigrange As Range

lastrow =
Workbooks("Book1.xls").Sheets("Sheet1").Range("A65 536").End(xlUp).Row
Set MyRange = Workbooks("Book1.xls").Sheets("Sheet1").Range("a1: a" & lastrow)

lastrow1 =
Workbooks("Book2.xls").Sheets("Sheet1").Range("A65 536").End(xlUp).Row
Set MyRange1 = Workbooks("Book2.xls").Sheets("Sheet1").Range("a1: a" & lastrow)

For Each c In MyRange
For Each c1 In MyRange1
If c.Value = c1.Value Then
If Bigrange Is Nothing Then
Set Bigrange = c.EntireRow
Else
Set Bigrange = Union(Bigrange, c.EntireRow)
End If
End If
Next
Next
Bigrange.Delete
End Sub

Mike


" wrote:

Hi all

I have a little problem, I have a list of values open in one
workbook, and another list of value in a second open workbook. What I
need to do is loop through each of the values with the first list, if
the value appears on the second list I then wish to delet the entire
row the value is on with the first list. Hope that makes sense.

I have a code that i have done, dont laugh! but this seems to take a
while to run, and I am sure theres a better way then what I am doing,
any help/suggestions greatly recieved

my codes

Range("e15").Select
Do Until ActiveCell = ""
orderno = ActiveCell
Windows("national calling list.xls").Activate
Range("d2").Select
Do Until ActiveCell = ""
If ActiveCell = orderno Then
check = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Windows("end user calling list ver8.xls").Activate
If check = 1 Then
ActiveCell.EntireRow.Delete
Else:
ActiveCell.Offset(1, 0).Select
End If
check = 0

Loop