# How to insert blank row after every nth row?

#### genetist

##### Board Regular
Hi to all,
I want to insert a blank row after every nth row for example after every 10th row. I s there any trick for this or we have to use macros? to insert rows automatically?

Thanks,
Regards,
Genetist

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### rjwebgraphix

##### Well-known Member
I don't think there's a way to do it without code, but this would do it....

Code:
``````Sub InsertRowEveryXrows()

Dim rw As Long
Dim lr As Long
Dim cnt As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
rw = 1
cnt = 1
Do
If cnt = 10 Then
Rows(rw).Insert Shift:=xlDown
cnt = 1
Else
cnt = cnt + 1
End If
rw = rw + 1
Loop While rw <> lr
End Sub``````
If you want a different count, just change this line....

Code:
``    If cnt = 10 Then``
to the number of rows you want to insert a row.

#### Michael M

##### Well-known Member
Also
Code:
``````Sub InsertRowEveryXrows()
Dim r As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For r = 10 To lr Step 10
Rows(r).Insert Shift:=xlDown
Next r
End Sub``````

#### kevatarvind

##### Well-known Member
TRY BELOW CODE ITS ALLOW TO INSERT ROW AS PER YOUR INPUT PROVIDE YOU

Code:
``````Sub Insert_Row()
Dim my As Integer, ur As Integer
On Error GoTo Getout
ur = InputBox("Enter How Many Row Want to Insert")
my = InputBox("Enter How Many Rows Want to Skip")
Application.ScreenUpdating = False
If my = 0 Or ur = 0 Then Exit Sub
On Error GoTo Getout
Range("A" & 2 + my).Select
Do While ActiveCell.Value <> ""
5 Range(ActiveCell, ActiveCell.Offset(ur - 1, 0)).EntireRow.Insert
ActiveCell.Offset(1 + my + ur - 1, 0).Select
Loop
Getout:
Application.ScreenUpdating = True
End Sub``````

#### rjwebgraphix

##### Well-known Member
That won't work as intended Michael. Tested on about 980 rows and with each insert the last row goes down by one, that's why I went with a do loop instead of a for loop. You can't recalculate the last row in the middle of a for loop.

Which reminds me.... I forgot the last row recalculation, so this one will work....

Code:
``````Sub InsertRowEveryXrows()

Dim rw As Long
Dim lr As Long
Dim cnt As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
rw = 1
cnt = 1
Do
If cnt = 10 Then
Rows(rw).Insert Shift:=xlDown
cnt = 1
lr = Range("A" & Rows.Count).End(xlUp).Row
Else
cnt = cnt + 1
End If
rw = rw + 1
Loop While rw <> lr
End Sub``````

I suppose you could use a for loop counting backwards, but then you'd have to do some calculating to make sure it does it on the correct rows to be every x number rows from the top. It's just easier with the do loop and using count variables, but I've often been proved that easier is not always better.

#### rjwebgraphix

##### Well-known Member
TRY BELOW CODE ITS ALLOW TO INSERT ROW AS PER YOUR INPUT PROVIDE YOU

Sorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40

#### Trebor76

##### Well-known Member
Hi rjwebgraphix

Try this:

Code:
``````Option Explicit
Sub Macro1()

'Written by Trebor76
'Visit my website www.excelguru.net.au

Dim lngMyRow As Long
Dim lngLastRow As Long
Dim rngMyRange As Range

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For lngMyRow = 10 To lngLastRow Step 10

If rngMyRange Is Nothing Then
Set rngMyRange = Cells(lngMyRow, "A")
Else
Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
End If

Next lngMyRow

rngMyRange.EntireRow.Insert

Application.ScreenUpdating = True

MsgBox "Rows have now been inserted"

End Sub``````

Regards,

Robert

#### kevatarvind

##### Well-known Member
Sorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40
sorry
rjwebgraphix
in my file daily i am using same code its working fine i dont know why its not working in yours ?

#### rjwebgraphix

##### Well-known Member
Hi rjwebgraphix
Try this:

Wasn't my post, just pointing out the flaws in the other codes. The do loop I wrote works. Yours has a similar effect as kev's, where every 10 rows should be inserted at row 10, 20, 30, 40. etc.... yours did 10, 21, 32, 43.

btw, I get a kick out of it whenever I see your posts. My Dad uses a variation of Trebor on dbforums and when I saw you post the first time I thought you were my dad. LOL

#### Trebor76

##### Well-known Member
Wasn't my post, just pointing out the flaws in the other codes.

Ah yes, sorry about that.

btw, I get a kick out of it whenever I see your posts. My Dad uses a variation of Trebor on dbforums and when I saw you post the first time I thought you were my dad. LOL

He's name Robert then? Must be a cool guy

Genetist, try this:

Code:
``````Option Explicit
Sub Macro1()

'Written by Trebor76
'Visit my website www.excelguru.net.au

Dim lngMyRow As Long
Dim lngLastRow As Long
Dim rngMyRange As Range

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For lngMyRow = 10 To lngLastRow Step 10

If lngMyRow <> 10 Then lngMyRow = lngMyRow - 1

If rngMyRange Is Nothing Then
Set rngMyRange = Cells(lngMyRow, "A")
Else
Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
End If

Next lngMyRow

rngMyRange.EntireRow.Insert

Application.ScreenUpdating = True

MsgBox "Rows have now been inserted"

End Sub``````

Replies
0
Views
154
Replies
3
Views
101
Replies
16
Views
227
Replies
7
Views
72
Replies
3
Views
75

1,172,131
Messages
5,879,230
Members
433,412
Latest member
diakaoma

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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