find/delete data on different tabs
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!!!
|