automatically insert rows in excel 2013

My dear!

Please help me some codes that can insert rows automatically following a certain division (as the attached sample, division is 12 cartons per row).

Thanks for your help.
Best regards,
Doco

how to insert row automatically.xlsx

Have a look at Sheet2
1. Is this how sheet1 should be after rows inserted?
2. What happens if column "D" does not divide by 12
eg row 17 , column D = 81. How many rows? 81/12 = 6 PLUS 1 = 7 rows?

If different please attach workbook showing how sheet1 should look
thanks

Run this and see if sheet 2 is what you are looking for.
(if not, please amend sheet2 to how you want it to look)

Correct number of rows being inserted
(but unsure if you want it to do anything else)

LastRow number hard coded because of extra data below the range operating in.
Various tricks like UsedRange.rows.count would give the incorrect answer.

When adding and deleting rows it is often better to start at the bottom of the range and work up
(avoids VBA re-numbering rows ahead of itself)

Please Login or Register  to view this content.

Originally Posted by Kevin#

Run this and see if sheet 2 is what you are looking for.
(if not, please amend sheet2 to how you want it to look)

Correct number of rows being inserted
(but unsure if you want it to do anything else)

LastRow number hard coded because of extra data below the range operating in.
Various tricks like UsedRange.rows.count would give the incorrect answer.

When adding and deleting rows it is often better to start at the bottom of the range and work up
(avoids VBA re-numbering rows ahead of itself)

Please Login or Register  to view this content.

Thank you Kevin!
I am trying to test your codes.
The rows of records are not always 68. each lot have various row number. So I need to count the rows firstly.

Thanks brother

I modify a little, then it work perfectly. Thank you Kevin!

Sub InsertRows()
Dim ws As Worksheet
Dim r, j As Long
Dim i As Integer
Set ws = ActiveSheet
firstrow = 3
LastRow = 68 '(please help me to count the row automatically and start inserting from the last record)
r = LastRow
keepTrying:
If r = 2 Then Exit Sub
j = ws.Cells(r, 4).Value
If j > 12 Then
i = Application.WorksheetFunction.RoundUp(j / 12, 0) - 1
ws.Rows(r + 1 & ":" & r + i).Insert Shift:=xlDown
End If
r = r - 1
GoTo keepTrying:
End Sub

You said
" How many rows? 81/12 = 6 PLUS 1 = 7 rows?"
Your amended the formula for "i" now will result in 6
Were you including the original row in your"How many rows?"


To fix LastRow calculation
Replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Thanks for your respond.
_ yes, 81/12 = 7 rows (1 original row + 6 inserted rows = 7)

_ Your suggestion codes for defining last row is miracle. Thanks.