CODEWITHSUNDEEP
excelvba
Ahmed
Ahmed

Reputation: 1533

Macro to add rows every other row

I have the following macro running on my Excel spreadsheet:

Sub yTest01()
    For i = 10 To 72 Step 2
        Cells(i, 1).EntireRow.Insert
    Next i
End Sub

It adds a new row every other row. However, I want to add five rows each. I tried the following but it doesn't work

Sub yTest01()
    For i = 10 To 72 Step 2
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
    Next i
End Sub

Any ideas what I can do?

Upvotes: 0

Views: 3131

Answers (3)

Excel Hero
Excel Hero

Reputation: 14764

As a best practice I strive to reduce the number of times VBA must communicate across the boundary to Excel.

So instead of making FIVE calls across the boundary for each insertion, it's better to make one. Stepping backwards can be helpful, but its not necessary.

For i = 0 To 62 * 6 Step 6
    [10:10].Resize(5).Offset(i).Insert
Next

Note: the iteration values of 0 and 62 are simply OP's row numbers adjusted to start at zero.

Upvotes: 2

Wouter
Wouter

Reputation: 383

why does it not work and why do you use step 2? You could move i 5 postitions after moving the rows?

Sub yTest01()
Dim i As Integer
    For i = 10 To 72 Step 1
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        i = i + 5
    Next i
End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152475

My guess is that you want one line of existing data then 5 rows of blank. Try the following

Sub yTest01()
For i = 72 To 10 Step -1
    Cells(i, 1).EntireRow.Insert
    Cells(i, 1).EntireRow.Insert
    Cells(i, 1).EntireRow.Insert
    Cells(i, 1).EntireRow.Insert
    Cells(i, 1).EntireRow.Insert
Next i
End Sub

if what you want is two rows of data then five empty lines then change the step to -2

When adding or subtracting rows it is best practice to loop backwards.

Upvotes: 2

Related Questions