Delete Row if Name String found in Range

[grady121]

I'm looking for a bit of code that will delete the Row if the required String is found within a Range for most worksheets in my workbook.

It starts with a ListBox populated with the names found within a Range in Sheet 1 - Column A
On selection, I have to manipulate the string to remove any other characters, after the second space, leaving just the initial name. This is because others add other characters on the end. e.g. "John Smith -21-" - becomes just "John Smith"

After confirming the selected name to be deleted, I'm looking for a bit of code that will look in the same Range as the selection and delete the Row, when found.

I'll later want to modify the range on "Data" sheet, but hope to modify the code later.

This is what I have so far:-

Private Sub UserForm_Initialize()

    Dim myCell As Range
    Dim myRng As Range

    Set myRng = Sheets(1).Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row)

    With ListBox1
        For Each myCell In myRng.Cells
        If Trim(myCell.Value) = "" Then
            'skip it
        Else
            .AddItem myCell.Value
        End If
        Next myCell
    End With
End Sub

Private Sub CommandButton1_Click()
    
    Dim i As Integer
    Dim iRet As Integer
    Dim NameSelected As String
    
    For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        Sheets(1).Range("G1").Value = ListBox1.List(i)
        With Sheets(1).Range("G1").Offset(0, 1)
            .Formula = "=LEFT(G1,FIND("" "",G1,FIND("" "",G1)+1)-1)"
        .Value = .Value
        End With
    End If
    Next i
    
    NameSelected = Sheets(1).Range("H1")

    iRet = MsgBox("You selected:-  " & NameSelected & vbCrLf & vbCrLf _
        & "Are you sure want to delete this Person ?", 36, "Please confirm")
        If iRet = 7 Then
            Unload Me
            MsgBox "Nothing deleted.", vbOKOnly, "You clicked No."
            Exit Sub
        Else
            Unload Me
        End If
    Application.ScreenUpdating = False

' ************************************************************

    Dim Ws As Worksheet
    
    For Each Ws In Worksheets
    Select Case Ws.Name
    Case "Notes" 'Do Nothing
    Case "Data" 'Do Nothing
    Case "Instructions" 'Do Nothing
    Case Else
        Ws.Select
        
    ' ***** Delete Row Code *****
    
    End Select
    Next Ws
    Sheets(1).Select
    
    Sheets(1).Range("H1") = ""
    Application.ScreenUpdating = True

    MsgBox NameSelected & " has been deleted.", vbOKOnly, "Done"
        
End Sub

Apologies if the code is a little raw, most of it was picked up elsewhere !

Any help appreciated

 

[grady121]

Apologies if this is a bit confusing.

To try and clarify. After modifying the original String selected from the ListBox, (and using a Formula to remove any added characters starting at the 2nd space) I wanted to use the result, I now call "NameSelected" as my search String. I may not have gone about this correctly, but my code above does seem to offer what I needed.

What I need now is the code that will simply look for the trimmed version I'm now calling "NameSelected" in any part of the cells, on most sheets found within Range A8 down to the bottom cell used in Column A. When it finds it, delete the appropriate Row. Later I'll can modify the range to work on the 'Data' sheet, which has a similar list of names in Range D3, down to the bottom cell used in Column D

Hope this clears it up, but if your still not sure what I'm trying to do, or can think of a better solution, then please reply.

Thanks again for looking in.