Delete row if value in another workbook
 

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.

Delete row if value in another workbook
 

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

Delete row if value in another workbook
 

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