Excel Vba for find values

Dannyws6

New Member
Joined
Nov 5, 2015
Messages
3
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try this on a copy of your workbook first to test it works as intended.

Code:
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
 
Upvote 0
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
 
Upvote 0
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"








Code:
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]
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top