Excel Vba for find values

[Dannyws6]

Here is the scenario. I have an excel workbook. One of the sheets is named "Part Tree" which contains a list of parts all over meaning they go from left to right and go all the way to row 533.

I have another sheet called "Sheet1" that has just a list of parts in column "A" from row 1 to 153. I can’t figure out how to create a vba to find the parts listed in Sheet1 in the "Part Tree" tab. I want to find those parts in "Sheet1" in the "Part Tree" tab and have them deleted. So i want to vba code to find the first part which could be listed multiple time in the "Part Tree" tab and delete that part then find the next part and delete that part and all its references and so on and so forth for the entire list in Sheet1.

Any help would be very appreciative.

 

[steve the fish]

Try this on a copy of your workbook first to test it works as intended.

Sub deleting()

Dim c As Range
Dim i As Long
Dim Findme As String

For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Findme = Sheets("Sheet1").Range("A" & i).Value
 
    With Sheets("Part Tree").Rows("1:533")
        Set c = .Find(Findme, LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not c Is Nothing Then
            Do
                c.ClearContents
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
    
Next

End Sub

 

[Dannyws6]

Thanks Steve the Fish. You're the man. that worked perfectly.

I changed one thing.

Sub deleting()
Dim c As Range
Dim i As Long
Dim Findme As String
For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Findme = Sheets("Sheet1").Range("A" & i).Value

With Sheets("Part Tree").Range("F2:AK576")
Set c = .Find(Findme, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

Next
End Sub

 

[Dannyws6]

Thanks for your help Steve. Question: I made a change so that if the code finds the part instead of clearing the contents then moving to the next part and clearing. Can i have it delete the entire row that each part number is in? So if the part is in row 10 it would delete the entire row. Then if the part is in row 25 samething. Then move to the next part and so on..

The change i made was changes the "Clearcontents" reference to "c.EntireRow.Delete". But now it stop at the next part to "FindNext"

Sub deleting()

Dim c As Range
Dim i As Long
Dim Findme As String

For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Findme = Sheets("Sheet1").Range("A" & i).Value
 
    With Sheets("Part Tree").Rows("1:533")
        Set c = .Find(Findme, LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not c Is Nothing Then
            Do
                [B]c.EntireRow.Delete[/B]
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
    
Next

End Sub

[/QUOTE]