On Mar 13, 10:27*am, Mike H <Mi...@discussions.microsoft.com> 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("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.willi...@bt.com" 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
|
Similar Threads
