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

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

On Mar 13, 10:27*am, Mike H wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Mike,

Just out of interest, could this work, if I did not know the name of
workbook 2, Just that theres was anoteher workbook open with a list
on, or work on a clsoed workbook

John