Adding 10 rows between each row in my original table in Excel VBA

Question

I have a table with data where I want to insert exactly 20 rows between each row of my original table. I have tried running nested for loops to add each row per loop and to hop onto the next "original" row on my table to add another 20 rows below it. However, this takes forever since I have over 2000 rows on my spreadsheet. Is there any other way to do this? Any vba code I could use for this?

`Screenupdating=false` at the beginning of your loop will speed this type of process up significantly. — Automate This, Nov 07 '13 at 15:27

Automate This · Accepted Answer · 2013-11-07T17:58:52.653

3

Try this:

Sub AddRows()
    ScreenUpdating = False

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Dim AddRows As Integer: AddRows = 10

    Dim i As Integer: i = lastrow

    Do While i <> 1
        Rows(i & ":" & i + AddRows - 1).Insert
        i = i - 1
    Loop

    ScreenUpdating = True
End Sub

edited Nov 07 '13 at 17:58

answered Nov 07 '13 at 15:55

Automate This

30,726
11
60
82

2

if you reverse the loop, then a counter is not needed – SeanC Nov 07 '13 at 16:43
Great Point! I'll update the answer. – Automate This Nov 07 '13 at 17:53
Or you could do it without a loop using SpecialCells – Our Man in Bananas Sep 15 '14 at 15:50

score 0 · Answer 2 · edited Nov 08 '13 at 10:12

0

basis of a solution (ie change upper and lower bounds of loop and or add them programatically)

For i = 10 To 1 Step -1
    Rows(i + 1 & ":" & i + 10).Insert
Next

edited Nov 08 '13 at 10:12

chris neilsen

52,446
10
84
123

answered Nov 07 '13 at 15:38

Loomah

1

should be "i+20" and i need to look at how formatting (or mark up) works on this site before i come back!! – Loomah Nov 07 '13 at 15:43

Adding 10 rows between each row in my original table in Excel VBA

2 Answers2