How to Insert Multiple Rows In Excel using VBA

Excel comes with much excellent inbuilt functionality that leads to automation of activities. This is actually one of the advantages of spreadsheets. Inserting many multiple rows at once in excel can now be done in a single click without actually inserting the rows one by one with the help of VBA codes and formulas. Inserting multiple rows in excel is thus one of the fantastic things that one can do. To insert rows in Excel, kindly follow the following procedure:

Steps to Insert Multiple Rows in Excel using VBA

1. Ensure that your excel work has a developer tab as follows. If not, activate it as follows:

2. After Looking for the developer tab and finding out that it does not exist by default please do the following. Press the "File" tab.

This will open a window that has a pane containing a list of commands on its left. Some of the commands include Save, save as, open, close, info, recent, new, print, save &send, Help, Options, and Exit.

From this list of commands that are actually descending from top to bottom select "Options" located towards the bottom left.

3. Another window with Excel options will appear with a similar layout to the previous one.

Click the "customize ribbon" option to open yet another window and select the developer checkbox as shown below. Then click OK. The Developer tab should now be activated.

4. Now click the developer tab on the ribbon.

5. Now navigate to the group named "code" and select the "Visual Basic" icon.

This icon is usually located near the macros tab.

A blank window named Microsoft Visual Basic for Applications" will appear.

6. Create a new module or optionally click sheet1 and Type the following code as it appears in the code editor as below:

The code is:

Sub insertMultipleRows()

'code disables excel while macro runs'

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Application.ScreenUpdating = False

Dim rowcount As Integer

rowcount = Application.InputBox(Prompt:="how many rows do you want to insert" & ActiveCell.Row & "?", Type:=1)

'handle error for instances when row count may be negative or zero'

If rowcount <= 0 Then End

Rows(ActiveCell.Row & ":" & ActiveCell.Row + rowcount – 1).Insert Shift:=xlDown

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

So the code will appear as follows in the VBA editor

7. Save your codes using the SAVE button. Note that you have to as a macro enable workbook for the code to work.

8. Run the macro by clicking the RUN tab while still in developer mode as follows.

You can optionally click the green right-pointing arrow located under Debug menu tab on the VBA ribbon.

9.  After clicking run a dialog box with the title "Input" will show up.

10. Enter the number of rows you want to insert and select "OK" after keying the number of rows to insert

%d bloggers like this: