vb for search and delete HELP!!

[theSTUDENT]

Okay here is what I need. I have an excel file with a BUNCH of records (2 columns and over 100,000 rows. I need a macro to search for a string in one of the columns and if that string is found then automatically delete that whole row. The main thing is I need to be able to search for a string that may vary in the beginning but has the same ending. For example, I need both of the following strings deleted - dtd44565 and jhk34565. Both strings end in 4565 which is the 4 digit plant location # but the preceding model #'s are different(dtd4) and (jhk3). I want the code to search for and delete both of these records. (something like %4565 only I don't know vb code so I don't know if there is a symbol that can be used to search for ANY string with 4565 at the end) Can somebody PLEASE HELP??

 

[AlphaFrog]

Hi and welcome.

• Select column A
• Select from the menu Data|Filter|Autofilter
• This will put a dropdown list in cell A1
• Select from the A1 dropdown list Custom
  • Select Ends With and type in 4565
  • OK

This will hide all the rows that don't end with 4565
You can then select the visible rows and delete them

Do the same for column B

 

[pushkardey]

Description: This code searches for string in the end of the Value in a cell and delete it if it is a match.

Sub chk_last_str()
Set w = ThisWorkbook
cl = InputBox("Enter Column to Search")
st = InputBox("Enter String to Search")
l = Len(st)
nodt = Application.WorksheetFunction.CountA(w.ActiveSheet.Range(cl & ":" & cl))
For lp1 = 1 To nodt
w.ActiveSheet.Range(cl & lp1).Activate
chkst = w.ActiveSheet.Range(cl & lp1)
    If Right(chkst, l) = st Then
        Rows(lp1 & ":" & lp1).Delete Shift:=xlUp
        lp1 = lp1 - 1
    End If
    If lp1 = Application.WorksheetFunction.CountA(w.ActiveSheet.Range(cl & ":" & cl)) Then GoTo 10
Next lp1
10
End Sub

Hope this helps.... mind the spacing issue...if you find any while pasting the code in your vb editor