VBA code to search a word and shift whole row to the new line

[mahiT]

Hello Everyone,

Please help me to get the VBA code to search a word and shift whole row to the new line.

 

[RudiS]

Can you provide more info.
In which column will the word be? and where must it shift to? (the last row; the next row??)

TX

 

[mahiT]

Thanks Rudi for instant reply.

I have a excel sheet where I manage the vehicle details to issue stickers/passes for the parking. In a sheet there are columns from starting like (Date, Company Name, Employee Name, Vehicle Reg No, Vehicle Type-2/4wheeler,Sticker number). If suppose I have already given a sticker to any particular Vehicle, so I have to find first 'Vehicle Reg No' in column D, if it is there then whole row (Except Sticker Number) to be shifted to new row line.

Hope you would understand this and waiting for your reply

 

[RudiS]

Here is some code you can work with.
Please note that it moves and ENTIRE row to the bottom of a list. I don't have enough details of your sheet to determine what you need when you say, "Except Sticker Number"...

Sub FindAndMove()
Dim Reg As String
Dim rF As Range
    
    Application.ScreenUpdating = False
    Reg = InputBox("Type vehicle registration number.")
    If Reg = "" Then Exit Sub
    With Range("D:D")
        Set rF = .Find(What:=Reg, LookAt:=xlPart, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=False)
        If Not rF Is Nothing Then
            rF.EntireRow.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
            rF.EntireRow.Delete
        Else
            MsgBox "The vehicle registration: " & Reg & " was not found!", vbExclamation
        End If
    End With
End Sub

 

[mahiT]

Here is some code you can work with.
Please note that it moves and ENTIRE row to the bottom of a list. I don't have enough details of your sheet to determine what you need when you say, "Except Sticker Number"...

Sub FindAndMove()
Dim Reg As String
Dim rF As Range
    
    Application.ScreenUpdating = False
    Reg = InputBox("Type vehicle registration number.")
    If Reg = "" Then Exit Sub
    With Range("D:D")
        Set rF = .Find(What:=Reg, LookAt:=xlPart, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=False)
        If Not rF Is Nothing Then
            rF.EntireRow.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
            rF.EntireRow.Delete
        Else
            MsgBox "The vehicle registration: " & Reg & " was not found!", vbExclamation
        End If
    End With
End Sub

Thank you Sir for the help and I had to say 'Except column F'...