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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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'...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,517
Messages
5,837,802
Members
430,516
Latest member
thaling

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