Lesson#176: Insert rows within a range with VBA

Lesson#176: Insert rows within a range with VBA
  1. Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
    • 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 also  Lesson#195: How to make a Basic calculator in VBA

    See here the result.

    Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *