Macro to find based on variable cell values in loop

[patsdavixen]

Hi,

I have two sheets in a workbook. The first sheet is named "A" and the second sheet is named "B". Both sheets are identical i.e. they have data ranging from Columns A:AI. However, the number of rows are variable.
I need a macro that will run in loop by looking up data that is mentioned in column D only (the number of rows are variable) on sheet "B" against Column D only in sheet "A". For example, if the word "Popcorn" is mentioned on sheet "B" in cell D2, the macro should look for "Popcorn" in column D in any row in sheet "A". If it finds the word "Popcorn" in sheet A, it should delete the entire row. Once it checks D2 in Sheet "B", it should move on to D3 and so on until it reaches a blank cell (the entire row will also be blank) in column D.
I have the following code but it only searches the cell value of D2. I do not know how to loop it and also have it search only for the range of D

Sub Find()
        Dim Found As Range
        Sheets("A").Select
        Set Found = Cells.Find(What:=Sheets("B").Range("D2").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        If Found Is Nothing Then
        MsgBox "Not found", vbInformation
        Exit Sub
        Else
        Found.Select
        Selection.EntireRow.Delete
        End If        
        
      End Sub

Any help would be greatly appreciated.

Thanks,
Pat

 

[kevatarvind]

try below code

Sub DEL_ROWS()
Sheets("A").Select
Range("D" & Rows.Count).End(xlUp).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Value = Sheets("B").Range("D2").Value Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Loop
End Sub

 

[patsdavixen]

Hi kevatarvind,

Thanks for replying to my post. The code you provided only checks the cell value of "D2", it does not continue to check all other cells in column D.
Is there a way for the macro to check D3, D4 and so on once it has checked D2? The macro should stop searching for the value of column D on sheet "B" against sheet "A" until it reaches a blank cell in Column D of sheet "B".

Thanks,
Pat

 

[kevatarvind]

Hi kevatarvind,

Thanks for replying to my post. The code you provided only checks the cell value of "D2", it does not continue to check all other cells in column D.
Is there a way for the macro to check D3, D4 and so on once it has checked D2? The macro should stop searching for the value of column D on sheet "B" against sheet "A" until it reaches a blank cell in Column D of sheet "B".

Thanks,
Pat

oh sorry i did not get you ok now try below one i think its perfect what you need

Sub DEL_ROWS()
Dim myrng As Range
Set myrng = Sheets("B").Range("D2")
Do Until myrng.Value = ""
Sheets("A").Select
Range("D" & Rows.Count).End(xlUp).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Value = myrng.Value Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Loop
Set myrng = myrng.Offset(1, 0)
Loop
End Sub

 

[patsdavixen]

Kevatarvind, thank you so much for your help. This code is perfect. You're the best.