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

 

[Sektor]

With ThisWorkbook.Sheets("Sheet1").Range("A:A")
            .Find(what:=myString, After:=.Cells(1, 1), lookat:=xlWhole).EntireRow.Delete
        End With

 

[ExcelJohn]

Hi Sektor, and thanks.

This indeed removes the whole line in Sheet1, but only copies the cell value to Sheet2. I would like to "move" the whole line, not to "remove".

Do you know how to do that ?

Best,
Marc

 

[Sektor]

Move IS deleting! Say, when you MOVE some file from one location to another, the OS first makes copy of that file and then deletes it. The same process is in Excel.
If you have some other meaning of "move", please be more specific.

 

[ExcelJohn]

Hey Sektor!

You are right, but :

the OS first makes copy of that file and then deletes it. The same process is in Excel.

So we agree that MOVE is first copy, and then remove.

The code does remove the whole Row from Sheet1 (ColA, ColB, ColC, etc...), but only copies to Sheet2 the ColA value, thus, missing ColB, ColC, etc...

This is what i mean.

 

[Sektor]

Try this:

    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
        With Sheets("Sheet1").Range("A:A")
            Set foundCell = .Find(What:=myString, After:=.Cells(1, 1), LookAt:=xlWhole)
        End With
        
    
        With Sheets("Sheet2")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range( _
                Sheets("Sheet1").Cells(foundCell.Row, "A"), Sheets("Sheet1").Cells(foundCell.Row, Columns.Count))
        End With

 

[ExcelJohn]

Hi Sektor,

Thanks.

It detects if it finds the value or not, but unfortunately the code is not working. It doesn't copy not remove the rows.

 

[Sektor]

Then try this:

    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

 

[ExcelJohn]

YES! It works!! Thanks a lot man! You made my day!

 

[Sektor]

Glad that helped!