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

A1
A2	ID	Name	Score
A3
A4	001	BEN	5

<tbody>
</tbody>

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.