top of page

vba code to add X rows for each selected row

I devised the below vba code, (a modified version of the code posted here: ) to automatically enter X number of rows after each selected row on a worksheet.

Change the number at the end of


to set the number of rows you want to insert, then increase the number after:

ActiveCell.Offset(7, 0)

by one more number than the number of rows you want to add.

Sub InsertAlternateRows()

'This code will insert a row after every row in the selection

'This code has been created by Sumit Bansal from

Dim rng As Range

Dim CountRow As Integer

Dim i As Integer

Set rng = Selection

CountRow = rng.EntireRow.Count

For i = 1 To CountRow

ActiveCell.Offset(1, 0).EntireRow.Resize(6).Insert

ActiveCell.Offset(7, 0).Select

Next i

End Sub


bottom of page