VBA : Find a string in Sheet1/ColA, move that whole row to Sheet2

[ExcelJohn]

Dear All,

I have the following code (see below) that prompts the user for a Text string, then it searches for this string in Sheet1/ColA, and then moves that cell to Sheet2.

The problem is that I would like to move the whole row where the matched cell is contained, not only the cell itself.

How would you ammend the code to do that ?

Any help would be appreciated.

Thanks.

    Dim myString As String
    Dim foundCell As Variant
    
    myString = Trim(UserForm2.TextBox1.Value)
    If myString = vbNullString Then
        Exit Sub
    End If
    
    On Error GoTo ErrorOut
        With ThisWorkbook.Sheets("Sheet1").Range("A:A")
            .Find(what:=myString, After:=.Cells(1, 1), lookat:=xlWhole).Delete shift:=xlUp
        End With
    
        With ThisWorkbook.Sheets("Sheet2")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = myString
        End With

 

[ExcelJohn]

Hi Sektor,

The code finally worked great. This is the final result :

Private Sub CommandButton1_Click()
    Dim myString As String
    Dim foundCell As Range
    
    myString = Trim(UserForm2.TextBox1.Value)
    If myString = vbNullString Then
        Exit Sub
    End If
    
    On Error GoTo ErrorOut
    
        ' Find and copy entire row.
        With Sheets("Sheet1").Range("A:A")
            Set foundCell = .Find(What:=myString, After:=.Cells(1, 1), LookAt:=xlWhole)
            foundCell.EntireRow.Copy
        End With
    
        ' Paste copied cell.
        Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
    
        ' Delete entire row of found range.
        foundCell.EntireRow.Delete
        
        UserForm2.Label2.Caption = "Record " & myString & " moved from Sheet1 to Sheet2"
        Exit Sub
        
ErrorOut:
    UserForm2.Label2.Caption = "Record " & myString & " doesn't exist in Sheet1 col A"
    On Error GoTo 0
End Sub

I would like to know how to adapt that code to use a multiline TextBox, so I can do the same procedure for a large amount of strings (one per line). I know how to tweak the properties of the textbox to make it multiline, but I don't know how to turn the code into a loop that does the same, one by one, to all of them.

Could someone please give me a hand ?

Thanks!

 

[Sektor]

I created test form and put TextBox and CommandButton. TextBox has Multiline = True and EnterKeyBehaviour = True. CommandButton handler:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] CommandButton1_Click()
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    arr = Split(TextBox1.Text, vbLf)
    [COLOR="Blue"]For[/COLOR] i = LBound(arr) [COLOR="Blue"]To[/COLOR] UBound(arr)
        MsgBox arr(i)
    [COLOR="Blue"]Next[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[ExcelJohn]

Thanks Sektor!

 

[Sektor]

Not at all!