Move row to bottom of range is cell value is zero

[RJBabin]

I have a set number of rows between 1 and 8. If the cell value in column C changes to zero then I would like to move this row to the last row which is 8 or maybe below the last row with a value >0 and move the others up. I do not want to re-sort the other rows. I need to keep them in the same order. I only want to take that one row with zero value and move it down. Example 1 below. There could be cases where the zero value in column C is in between two rows with values, Example 2 below. Thanks in advance for any help.

Example 1
Row 1 100
Row 2 200
Row 3 0
Row 4 300
Row 5 400
Row 6 0
Row 7 0
Row 8 0

Becomes
Row 1 100
Row 2 200
Row 3 300
Row 4 400
Row 5 0
Row 6 0
Row 7 0
Row 8 0


Example 2
Row 1 100
Row 2 200
Row 3 0
Row 4 300
Row 5 0
Row 6 400
Row 7 0
Row 8 0

Becomes
Row 1 100
Row 2 200
Row 3 300
Row 4 400
Row 5 0
Row 6 0
Row 7 0
Row 8 0

 

[myall_blues]

Try this. This is not the most efficient code in the world so if you will end up extending to a lot more than 8 rows it probably needs a rethink, but for a small number it will work fine.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Intersect(Target, Range("C1:C8")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For i = 8 To 1 Step -1
        If Cells(i, 3).Value = 0 Then
            Cells(i, 3).EntireRow.Copy Destination:=Range("A9")
            Cells(i, 3).EntireRow.Delete
        End If
    Next
    Application.EnableEvents = True
End Sub

This code needs to go in the Worksheet_Change event of your worksheet. To do that open the VBA editor (ALT+F11) and on the left hand side right-click on the sheet you're working in and click View Code. Then paste the code in the window that appears.

 

[Peter_SSs]

If the cell value in column C changes to zero

Would that be changed manually or does column C contain formulas?

 

[RJBabin]

Sorry, I forgot to mention that the cell in column C is a sum formula for other cells in that row. When the numbers change in the other cells and the cell in column C equals zero it needs to move to the bottom.

 

[Peter_SSs]

the cell in column C is a sum formula for other cells in that row.

1. Can you give us that sum formula so we can see how many and what other cells in the row feed into it?
2. Do the cells that are summed by that sum formula in column C get changed manually or are they also formulas? If formulas, can we see the formulas?

Check out XL2BB for providing sample data/formulas in a way we can easily copy for testing.
(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[RJBabin]

Thanks for the reply.
1. This formula is in each cell the the column C range =SUM(G14:BG14). Columns D, E and F contain names and categories. It is a manpower forecast.
2. The cells vallue gets changed by that formula.

 

[Peter_SSs]

2. The cells vallue gets changed by that formula.

You misinterpreted my second question. I am asking how G14:BG14 get changed. Are they changed manually or are they also formulas? If formulas, can we see the formulas?
I am trying to determine the best way to trigger the code to do the job you want done.