Find a row and move it to the end

H

HelpMe

Hi,

I would like to find a value in column A that is = "INIT", once that is
found, I need the entire row moved to the bottom of the sheet. This row needs
to become the last row and row be deleted from where it was found initially
I will only have one "INIT" value in column A so as soon as that is found, I
need to move the row.

Please please help!
 
S

Simon Lloyd

HelpMe;344735 said:
Hi,

I would like to find a value in column A that is = "INIT", once that is
found, I need the entire row moved to the bottom of the sheet. This row
needs
to become the last row and row be deleted from where it was found
initially
I will only have one "INIT" value in column A so as soon as that is
found, I
need to move the row.

Please please help!This should do what you need:
Click to expand...


Code:
--------------------
Sub Find_move_n_delete()
Dim FndCel As String
FndCel = Columns(1).Find(What:="INIT", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
Range(FndCel).EntireRow.Copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range(FndCel).EntireRow.Delete Shift:=xlUp
End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
A

AltaEgo

This will move the INIT row from wherever it is in row A to the bottom of
row A, closing the gap where it was.

Sub MoveINITrow()

Columns("A:A").Select
Selection.Find(What:="INIT").Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Selection.End(xlDown).Offset(1).Select
Selection.Insert Shift:=xlDown
End Sub


As required, change the above line
Selection.Find(What:="INIT").Activate

AS below:

If you need to match case
Selection.Find(What:="INIT", MatchCase:=True).Activate

Find entire word
Selection.Find(What:="INIT",LookAt:=xlWhole).Activate

Both
Selection.Find(What:="INIT",LookAt:=xlWhole, MatchCase:=True).Activate
 
O

OssieMac

Sub Move_Row()

Dim rngColA As Range
Dim rngToFind As Range
Dim strTofind As String

strTofind = "INIT"

With Sheets("Sheet1")
Set rngColA = .Columns("A:A")
Set rngToFind = rngColA.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngToFind Is Nothing Then
rngToFind.EntireRow.Cut
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Insert
Else
MsgBox "Did not find " & strTofind
End If
End With

End Sub
 
Top