Data checking- a better way!

[john.9.williams]

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

 

[Mike H]

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("A65536").End(xlUp).Row
Set MyRange = Workbooks("Book1.xls").Sheets("Sheet1").Range("a1:a" & lastrow)

lastrow1 =
Workbooks("Book2.xls").Sheets("Sheet1").Range("A65536").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

 

[john.9.williams]

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("A65536").End(xlUp).Row
Set MyRange = Workbooks("Book1.xls").Sheets("Sheet1").Range("a1:a" & lastrow)

lastrow1 =
Workbooks("Book2.xls").Sheets("Sheet1").Range("A65536").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










- 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