Insert blank rows at specific intervals

Question

I'm trying to insert a set number of rows every row. I.e. spread my condensed data out.

The code below worked with variables as int's but since changing to long due to dataset size it has seemed to have broken. It just pushes all the rows from row 2 down a few thousand as a single group. The goal is to have 74 lines in between my existing data set

Sub Run()

Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Long
Dim xNum1 As Long
Dim xNum2 As Long
Dim WorkRng As Range
Dim xWs As Worksheet
Dim xLast As Long


xTitleId = "Input"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

xRowsCount = WorkRng.Rows.count

xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)

xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)

xNum1 = WorkRng.Row + xInterval

Set xWs = WorkRng.Parent

For i = 1 To Int(xRowsCount / xInterval)
    xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
    Application.Selection.EntireRow.Insert
    xNum1 = xNum1 + xNum2
Next

End Sub

Hey @SJR could you explain how the loop is backwards, it does seem to work as its intended By changing to xNum to long and the loop to be cLng it fixed the problem — cd-6
– cd-6, Commented Aug 29, 2019 at 15:48
Also, thanks @Warcupine for that pick up, no need to include it unless the interval needs to get bigger each time :) — cd-6
– cd-6, Commented Aug 29, 2019 at 15:49
If you loop from 1 to 10 and are inserting rows your last row will have moved beyond 10 but your loop will stop there, leaving the rest of your data unaffected. — SJR
– SJR, Commented Aug 29, 2019 at 15:50

SJR · Accepted Answer · 2019-08-29 15:27:49Z

1

Can you not do it like this?

Sub Run()

Dim xInterval As Long
Dim xRows As Long, i As Long
Dim xRowsCount As Long
Dim WorkRng As Range
Dim xWs As Worksheet
Dim xTitleId As String

xTitleId = "Input"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

xRowsCount = WorkRng.Rows.Count                                                                     '100
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)                      '5
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)    '3

Set xWs = WorkRng.Parent

For i = xRowsCount To 1 Step (-1 * xInterval)
    xWs.Cells(i, 1).Resize(xRows).EntireRow.Insert
Next

End Sub

answered Aug 29, 2019 at 15:27

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cd-6 Over a year ago

Hey @SJR this did the trick but also changing original code to xNum as Long and cLng in the loop fixed it

SJR Over a year ago

Glad it worked. Yes, always use Long rather than Integer.

Collectives™ on Stack Overflow

Insert blank rows at specific intervals

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related