Deleting a row based on value in another tab using VBA

[brantk]

I have seen numerous threads that touch on this topic, but can't find the exact code or figure out how to modify to work in my case.

I have 2 tabs (A & B)

I want to enter a value in tab A and delete the row in tab B that contains that value.

For example, if I enter a VIN number in tab A, I want it to search column A in tab B to delete the entire row of the match. There does not need to be any loops, as there will always only be one match.

Thanks in advance

 

[JoeMo]

This is sheet code for tab A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim Fnd As Range
For Each c In Target
    If c.Value <> "" Then
        With Sheets("B")
            Set Fnd = .Cells.Find(c.Value, , xlFormulas, xlWhole, , , False)
            If Not Fnd Is Nothing Then Fnd.EntireRow.Delete
        End With
    End If
Next c
End Sub

 

[My Aswer Is This]

Assuming you will be entering search value in column A
And when you say Tab A and B I assume you mean sheet(1) and Sheet(2)
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/30/2019  4:51:48 PM  EST
If Target.Column = 1 Then
Dim SearchString As String
Dim r As Long
Dim SearchRange As Range
SearchString = Target.Value
Dim lastrow As Long
lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
r = SearchRange.Row
Sheets(2).Rows(r).Delete
End If
End Sub

 

[brantk]

Thank you both for this. I was able to work with a co-worker to modify it to fit our needs and have this completed.