How to Insert a Specific Number of Rows at Once in Excel

Inserting rows in Excel worksheet is a common task that many users do every day. inserting a single row is simple and straightforward, but inserting multiple rows in the right places within your worksheet can sometimes be challenging. In this detailed guide, we’re going to show you how to insert specific number of rows in Excel worksheet using VBA Code.

Insert Multiple Rows in Excel

By the end of this tutorial, you’ll be able to insert multiple rows in excel worksheet with just one click.

Steps to automate the process of inserting Multiple rows in Excel Worksheet at once using VBA

Open your Excel Worksheet

Insert Specific Number of Rows in Excel Worksheet

Press ALT + F11 Shortcut Key to Open VBA Editor

Create a new module: from the toolbar (in VBA Editor) click on insert, then click on module option.

Insert Multiple Rows at Once in Excel

Copy and paste one of the following code snippet  into the module.

Sub insertRows1()
    Dim i As Long
    Dim j As Variant
    j = InputBox("How many rows would you like to insert?", "Insert Rows")
    If j = "" Then
        j = 1
    End If
    For i = 1 To j
        ActiveCell.Rows.EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
End Sub

OR

Sub InsertRows2()
    Dim AnchorRow As Integer, RowCount As Integer
    AnchorRow = ActiveCell.Row  
    RowCount = Application.InputBox(Prompt:="How many rows would you like to insert?", Type:=1)   
    Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert
End Sub

OR

Sub InsertRows3()
    Dim AnchorRow As Integer, RowCount As Integer
       AnchorRow = Application.InputBox(Prompt:="Please select a cell", Type:=8).Row   
    RowCount = 3 
    Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert
End Sub
VBA Code to Insert Multiple Rows at Once in Excel

After pasting the code press CTRL+S to save the Macros/VBA Code and Close the VBA Editor.

To execute the Macro press ALT+F8, Macros Dialog Box will open, containing list of Macros, select your macro and click on RUN Button.

RUN VBA Code to Insert Specific Number of Rows in Excel

I hope this tutorial will help to insert specified number of rows in excel worksheet using VBA.

If you have any query, suggestion or feedback feel free to contact us using the details provided on our contact us page.

Thank you for your feedback & support.

Similar Posts

Leave a Reply

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