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

mahiT

New Member
Joined
May 30, 2015
Messages
5
Hello Everyone,

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RudiS

Active Member
Joined
May 7, 2015
Messages
349
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

New Member
Joined
May 30, 2015
Messages
5
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:rolleyes:
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
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"...

Code:
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

New Member
Joined
May 30, 2015
Messages
5
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"...

Code:
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'...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top