# Insert new row based on condition ?

#### ramshakku

##### New Member
Before :-
 Item Number Qty to Produce Quanity Produced Remainng Quantity Date A 1000 400 600 28/09/2014 B 5200 3400 1800 28/09/2014 C 12000 7500 4500 28/09/2014 D 500 275 225 28/09/2014 E 8000 8000 0 28/09/2014 F 12000 9000 3000 28/09/2014

<tbody>
</tbody>

After :-

 Item Number Qty to Produce Quanity Produced Remainng Quantity Date A 1000 400 600 28/09/2014 A 600 29/09/2014 B 5200 3400 1800 28/09/2014 B 1800 29/09/2014 C 12000 7500 4500 28/09/2014 C 4500 29/08/2014 D 500 275 225 28/09/2014 D 225 29/08/2014 E 8000 8000 0 28/09/2014 F 12000 9000 3000 28/09/2014 F 3000 29/08/2014

<tbody>
</tbody>

If remaining quantity > 0, new line has to be created in the Excel along with Remaining Quantity.
How to achieve to my desired outcome ? Please do the needful.

Regards,
RAM

Last edited:

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### WarPigl3t

##### Well-known Member
Code:
``````firstRow = 2
lastRow = Range("A" & Rows.Count).End(xlup).Row
i = firstRow
Do Until i > lastRow
If Range("D" & i).value > 0 And _
Range("D" & i).value <> "" Then
i = i + 1
Range("A" & i).EntireRow.Insert
Range("A" & i).value = Range("A" & i -1).value
Range("B" & i).value = Range("D" & i - 1).value
Range("E" & i).value = Range("E" & i -1).value
lastRow = Range("A" & Rows.Count).End(xlup).Row
End If
i = i + 1
Loop``````

Last edited:

#### WarPigl3t

##### Well-known Member
This works too.
Code:
``````firstRow = 2
lastRow = Range("A" & Rows.Count).End(xlUp).Row
i = firstRow
Do Until i > lastRow
If Range("D" & i).Value > 0 And _
Range("D" & i).Value <> "" Then
i = i + 1
[COLOR=#ff0000]Rows(i).Insert[/COLOR]
Range("A" & i).Value = Range("A" & i - 1).Value
Range("B" & i).Value = Range("D" & i - 1).Value
Range("E" & i).Value = Range("E" & i - 1).Value
lastRow = Range("A" & Rows.Count).End(xlUp).Row
End If
i = i + 1
Loop``````

#### WarPigl3t

##### Well-known Member
You should use this code instead and here's why. If you run any of the two other codes 1 time, it will work. But if you run it again, it will create duplicates. Go ahead and give it a try and see so you know what I'm talking about. Use this code instead so that it makes sure you aren't creating a duplicate.
Code:
``````firstRow = 2
lastRow = Range("A" & Rows.Count).End(xlUp).Row
i = firstRow
Do Until i > lastRow
If Range("D" & i).Value > 0 And _
Range("D" & i).Value <> "" And_
[COLOR=#ff0000]Range("B" & i + 1).Value <> Range("D" & i).Value[/COLOR] Then
i = i + 1
[COLOR=#ff0000]Rows(i).Insert
[/COLOR]                     Range("A" & i).Value = Range("A" & i - 1).Value
Range("B" & i).Value = Range("D" & i - 1).Value
Range("E" & i).Value = Range("E" & i - 1).Value
lastRow = Range("A" & Rows.Count).End(xlUp).Row
End If
i = i + 1
Loop``````

#### ramshakku

##### New Member
Hi,

I am getting an following compilation error. (Invalid Outside Procedure)

-- removed inline image ---

This is my current sheet :-
 Item Number Qty to Produce Quanity Produced Remainng Quantity Date A 1000 1000 0 26/09/2014 A 2000 1000 1000 27/09/2014 A 1000 800 200 28/09/2014 B 5200 5200 0 27/09/2014 B 5200 3400 1800 28/09/2014 C 12000 12000 0 27/09/2014 C 12000 6000 6000 28/09/2014 D 500 500 0 27/09/2014 D 1000 275 725 28/09/2014 E 8000 8000 0 27/09/2014 E 12000 8000 4000 28/09/2014 F 9000 9000 0 26/09/2014 F 8000 6000 2000 27/09/2014 F 12000 9000 3000 28/09/2014

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

My expected Outcome :-
 Item Number Qty to Produce Quanity Produced Remainng Quantity Date A 1000 1000 0 26/09/2014 A 2000 1000 1000 27/09/2014 A 1000 800 200 28/09/2014 A 200 29/09/2014 B 5200 5200 0 27/09/2014 B 5200 3400 1800 28/09/2014 B 1800 29/09/2014 C 12000 12000 0 27/09/2014 C 12000 6000 6000 28/09/2014 C 6000 29/09/2014 D 500 500 0 27/09/2014 D 1000 275 725 28/09/2014 D 725 29/09/2014 E 8000 8000 0 27/09/2014 E 12000 8000 4000 28/09/2014 E 4000 29/09/2014 F 9000 9000 0 26/09/2014 F 8000 6000 2000 27/09/2014 F 12000 9000 3000 28/09/2014 F 3000 29/09/2014

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Regards,
RAM

#### WarPigl3t

##### Well-known Member
Please run the code using the F8 button. Do not run the code using the Run button. Then you can tell me exactly which line of code brings back the error. Even better, you can google ScreanOMatic and record your screen while you do this. It will upload the video to YouTube and you can post the link here so I can see what you did. What you asked for is extremely easy and so I am assuming it was a user error or you forgot to mention something. It would be helpful to figure out the source of the problem if you record what you did using ScreanOMatic or some other recording software.

Replies
9
Views
140
Replies
0
Views
88
Replies
3
Views
212
Replies
6
Views
164
Replies
3
Views
378

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back