Insert blank row after every nth row

[unknownymous]

Hello Guys,

I have long list of data and I want to insert a blank row after every nth row for example after. Below is the sample initial illustration. I need to add starting


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]001[/TD]
[TD]BEN[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


The data starts in A4 onwards and what I need is to add blank in every 5th row. Can you possibly help me modify below codes?

Sub InsertRowEveryXrows()
Dim r As Long, lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For r = 4 To lr Step 10
Rows(r).Insert Shift:=xlDown
Next r
End Sub



Any help will be much appreciated (beginner here). Thanks!

 

[JoeMo]

Your title calls for inserting a blank ROW, while your posted code inserts a blank cell in the row. The code below inserts blank cells, but see the comment if you want to insert entire rows.

Sub EveryNthRowInsert()
Const n As Long = 5 'set n here
Dim R As Range, InsertRng As Range
Set R = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For i = n To R.Rows.Count Step n
    If Not InsertRng Is Nothing Then
        Set InsertRng = Union(InsertRng, R(i))
    Else
        Set InsertRng = R(i)
    End If
Next i
Application.ScreenUpdating = False
If Not InsertRng Is Nothing Then InsertRng.Insert shift:=xlDown  'Change to Insertrng.EntireRow.Insert if you want to insert entire rows
Application.ScreenUpdating = True
End Sub

 

[unknownymous]

Thanks for this Joe. I use below codes:

Sub EveryNthRowInsert()
Const n As Long = 5 'set n here
Dim R As Range, InsertRng As Range
Set R = Range("c5:c" & Cells(Rows.Count, "c").End(xlUp).Row)
For i = n To R.Rows.Count Step n
If Not InsertRng Is Nothing Then
Set InsertRng = Union(InsertRng, R(i))
Else
Set InsertRng = R(i)
End If
Next i
Application.ScreenUpdating = False
If Not InsertRng Is Nothing Then InsertRng.EntireRow.Insert shift:=xlDown 'Change to Insertrng.EntireRow.Insert if you want to insert entire rows
Application.ScreenUpdating = True
End Sub


One more thing, what if I want to do below actions:

Let's say:

From A4 to A8 - A blank row in A9
From A10 to A19 - A blank row in A20
From A21to A40 - A blank row in A41


Thanks a lot in advance for the help.

 

[JoeMo]

Thanks for this Joe.

One more thing, what if I want to do below actions:

Let's say:

From A4 to A8 - A blank row in A9
From A10 to A19 - A blank row in A20
From A21to A40 - A blank row in A41


Thanks a lot in advance for the help.

You are welcome.

What's the logic you are using to choose those rows? Doesn't appear they follow any consistent pattern.