Hi John,

I'm getting a "Subscript out of range" error when the code hits
Set ws1 = .Worksheets("untitled")

i moved that worksheet so it's the first worksheet but the error still
comes up. I only have the two worksheets in the workbook.

Thoughts??

"john" wrote:

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!