Move row to bottom +1

mealypudding

New Member
Joined
Apr 4, 2011
Messages
8
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
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Apr 4, 2011
Messages
8
Hey thanks for you reply,
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


Thanks for your assistance
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top