Delete row if value in another workbook

[d4m]

I have 2 workbooks. 1 has a list of PC's and other data. The 2nd one
has a list of PC's that if they exist in workbook1, then delete the row
in workbook1. Is there a way to do this in VBA?

Thanks for anyone who can help this newbie.

[Dave Peterson]

Option Explicit
Sub testme()

Dim rng1 As Range
Dim rng2 As Range
Dim res As Variant
Dim myCell As Range
Dim DelRng As Range

With Workbooks("book1.xls").Worksheets("sheet1")
Set rng1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Workbooks("book2.xls").Worksheets("sheet1")
Set rng2 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In rng1.Cells
res = Application.Match(myCell.Value, rng2, 0)
If IsNumeric(res) Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
DelRng.Select
'or (after testing!)
'DelRng.EntireRow.Delete
End If

End Sub



d4m wrote:
>
> I have 2 workbooks. 1 has a list of PC's and other data. The 2nd one
> has a list of PC's that if they exist in workbook1, then delete the row
> in workbook1. Is there a way to do this in VBA?
>
> Thanks for anyone who can help this newbie.

--

Dave Peterson

[d4m]

Works Great...Thank You very much!
Dave Peterson wrote:
> Option Explicit
> Sub testme()
>
> Dim rng1 As Range
> Dim rng2 As Range
> Dim res As Variant
> Dim myCell As Range
> Dim DelRng As Range
>
> With Workbooks("book1.xls").Worksheets("sheet1")
> Set rng1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> With Workbooks("book2.xls").Worksheets("sheet1")
> Set rng2 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> For Each myCell In rng1.Cells
> res = Application.Match(myCell.Value, rng2, 0)
> If IsNumeric(res) Then
> If DelRng Is Nothing Then
> Set DelRng = myCell
> Else
> Set DelRng = Union(myCell, DelRng)
> End If
> End If
> Next myCell
>
> If DelRng Is Nothing Then
> 'do nothing
> Else
> DelRng.Select
> 'or (after testing!)
> 'DelRng.EntireRow.Delete
> End If
>
> End Sub
>
>
>
> d4m wrote:
> >
> > I have 2 workbooks. 1 has a list of PC's and other data. The 2nd one
> > has a list of PC's that if they exist in workbook1, then delete the row
> > in workbook1. Is there a way to do this in VBA?
> >
> > Thanks for anyone who can help this newbie.
>
> --
>
> Dave Peterson