You are currently viewing How to Insert Multiple Rows In Excel With Formula

How to Insert Multiple Rows In Excel With Formula

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:

Step 1:
Ensure that MS Excel is installed in your laptop or pc computer.

Without MS Office installed into your computer, it might be difficult to run this tutorial on your computer or pc. Ensure also that your OFFICE SUIT is activated for maximum user experience.

Step 2:
Search for Excel or optionally use a combination of windows + Q to open Search functionality. Type Excel; and this will show a list of programs.

Excel should be among the top of the list if you do not have many other programs whose name start with letter E.

It's important to ensure that the search is on the APPS option, and especially for windows 8 users, to located EXCEL which is an application.

You can also navigate to its location manually if you like. However, the search functionality will save you precious time.

Step 3:
Click on Excels icon to load the program into memory for use.

The icon is usually stripped in green, a color which is much easier to identify by anyone.

Step 4: Please Wait for Excel to load its interactive user interface.

The loading time varies for different machines but for almost fifty seconds is usually enough for MS Excel to be loaded into memory for display.

Step 5:
Open the file of your choice that you want to edit.

This is achieved by opening the file explorer via the File tab. Then "click on open". A dialog as below appears.

The file explorer utility is also a useful aspect that enables files of any type to be located easily. You can also choose to locate the document file of your choice using the "Recent option".

To use this click on the File tab in the ribbon and then click Recent.

A list of all documents that have worked on lately will be displayed. However in case you have just received the file from external sources and you have never worked on the Excel file before, the file will not appear in the recent section.

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

Step 7:
After Looking for the developer tab and find out that it does not exist by default please do the following. Press "File" tab.

This will open a window that has 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.

Step 8:
Another window with Excel options will appear with a similar layout to the previous one.

Select the "customize Ribbon" option. A list box with main tabs to be included and one that has checkboxes against their names will be displayed towards the right side of the window.

Click on the developer checkbox to turn on developer capabilities and then select the OK button to effect the changes.

The changes should now be saved on pressing the OK button and this will so close the customization window. You will then be taken back to the excel worksheet that you were working on with the developer tab enabled.

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

Step 9:
Now click the developer tab on the ribbon.

Step 10: Now navigate to the group named "code" and select "Visual Basic" icon.

This icon is usually located near the macros tab.

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

Step 11: 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 VBA editor

Step 12:
Save your codes using the SAVE button.

Step 13:
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 VBA ribbon.

Step 14: After clicking run a dialog box with the title "Input" will show up.

Step 15:
Enter the number of rows you want to insert and select "OK" after keying number of rows to insert

Step 16: The rows should now be inserted at the place where your active cell pointer was. In our case, 5 rows have been inserted from cell B3.