Lesson#176: Insert rows within a range with VBA

1. Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
2. Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.

To create a program in Visual Basic for Applications (VBA) that inserts a specified number of rows after every row in a specified range, you can use the following code:

Sub InsertRows(rng As Range, numRows As Long)

' Loop through the rows in the specified range
For Each row In rng.Rows
    ' Insert the specified number of rows after the current row
    row.Offset(1, 0).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next row

End Sub

This code defines a subroutine called InsertRows that takes a range and a number of rows as input parameters. It loops through the rows in the range and, for each row, it inserts the specified number of rows after the current row using the Insert method of the Range object.

To use this code, you will need to place the InsertRows subroutine in a VBA module in your Excel workbook and then call it from another subroutine or from a button click event. For example, you could use the following code to insert 2 rows after every row in a specified range when a button is clicked:

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = Selection ' Set the range to the current selection

Call InsertRows(rng, 2)

End Sub

This will insert 2 rows after every row in the currently selected range when the button is clicked. You can also specify a different range by setting the rng variable to a different range object, and you can change the number of rows to be inserted by modifying the value of the numRows parameter.

See here the result.