Move row to bottom +1

[mealypudding]

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

 

[Marcelo Branco]

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]

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

 

[Marcelo Branco]

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)

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]

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>