# Move row to bottom +1

#### mealypudding

##### New Member
Hello, using xp and excel 07. I have a rows of information but I would like a formulae that if column G = string "BREAKFAST INC - COM" It will move it to the bottom of the list and leave a space for the first one, any more will be placed underneath this.

Eg input

E G
1 SMITH Breakfast
2 DOE Breakfast inc - com
3 BOB Breakfast inc - com
4 PAUL Breakfast

Eg output

E G
1 SMITH Breakfast
2 PAUL Breakfast
3
4 DOE Breakfast inc - com
5 BOB Breakfast inc - com

I've tried to copy similar code and write my own but failed. I'm admitting defeat and passing it on to the professionals.

Any help would be greatly appriciated

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Marcelo Branco

##### MrExcel MVP
Hi,

One question:
Do you want to move down the entire row, if G = Breakfast inc - com, or just the cells in column G?

M.

#### mealypudding

##### New Member
Ive actually managed to find this and tweek it to work for me:

Set myRng = Range("A1").CurrentRegion
FirstRow = myRng.Row
Lastrow = FirstRow + myRng.Rows.Count - 1
For rw = Lastrow To FirstRow Step -1
If Cells(rw, "G") = "BREAKFAST INC - COM" Then
Rows(rw).Cut Cells(Rows.Count, "A").End(xlUp).Offset(1)
Rows(rw).Delete Shift:=xlUp
End If
Next rw

I now just need to break up the data by adding 2 rows to separate the breakfast and breakfast inclusive and to sum them up.

Currently Im using this to break the rows:

Cells.Find(what:="BREAKFAST INC - COM", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert

All I need now if to be able to sumup column F which is a number but im finding it difficult

Input:
SMITH 2 BREAKFAST
SMITH 2 BREAKFAST

SMITH 1 BREAKFAST INC - COM
SMITH 3 BREAKFAST INC - COM

Output:
SMITH 2 BREAKFAST
SMITH 2 BREAKFAST
sssssss4

SMITH 1 BREAKFAST INC - COM
SMITH 1 BREAKFAST INC - COM
sssssss2

Last edited:

#### Marcelo Branco

##### MrExcel MVP
Hi,

The macro below copy down the entire rows with "Breakfast inc - com" in column G and deletes the original row.

See if this is what you need.

Try it on a test-workbook
Change the worksheet-name accordingly (i used Plan3)

Code:
``````Sub moveDownRows()
Dim counter As Long, lastRow As Long, i As Long, lin As Long
Dim str1 As String
Dim wk As Worksheet

str1 = "Breakfast inc - com"

Set wk = Sheets("Plan3")

With wk
lastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
counter = Application.WorksheetFunction.CountIf(.Range("G1:G" & lastRow), str1)

lin = lastRow + 1 + counter
For i = lastRow To 1 Step -1
If .Range("G" & i) = str1 Then
.Rows(i).Copy Destination:=.Rows(lin)
.Rows(i).EntireRow.Delete
lin = lin - 2
End If
Next i
End With

End Sub``````

HTH

M.

#### Peter_SSs

##### MrExcel MVP, Moderator
Here's an alternative approach for the first part of the problem. It assumes ..

- Data starts in row 1 with no headings (guessing from your first code).

- 'Breakfast inc - com' entries, if they exist, are in column F.

Test in a copy of your workbook.

I haven't attempted the second part yet since your columns do not seem consistent between post #1 and post #3. You need to clarify what is in what columns.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Const</SPAN> s1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Breakfast inc - com"<br>    <SPAN style="color:#00007F">Const</SPAN> s2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "zzzzz"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("F")<br>        .Replace What:=s1, Replacement:=s2, _<br>            LookAt:=xlWhole, MatchCase:=False, _<br>            SearchFormat:=False, ReplaceFormat:=False<br>        .Cells(1, 1).CurrentRegion.Sort Key1:=Range("F1"), _<br>            Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _<br>            MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>        .Replace What:=s2, Replacement:=s1, _<br>            LookAt:=xlWhole, MatchCase:=False, _<br>            SearchFormat:=False, ReplaceFormat:=False<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .Find(What:=s1, After:=.Cells(1, 1), LookIn:=xlValues, _<br>            LookAt:=xlWhole, Searchdirection:=xlNext, MatchCase:=False, _<br>            SearchFormat:=False).Resize(2).EntireRow.Insert<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Replies
3
Views
40
Replies
2
Views
45
Replies
3
Views
54
Replies
1
Views
33
Replies
4
Views
137