Excel Macro to Insert Rows (8 Methods)

We use Excel in our everyday life. We perform data storage, calculation, and get information from Excel. If we have any task to do repeatedly in Excel, we can record a macro to automate those tasks. In this article, we will discuss Excel Macro to insert rows.

We will take a data set of names and ages of employees of a stationery shop.

Data set for Excel Macro to insert rows


What is Macro?

A macro is an action or a set of actions that are saved on Excel. We can run that macro as many times as we want. A macro is created by recording our mouse clicks and keystrokes. After we create a macro, we can edit it to make minor changes to the way it works.

How can we avail Macro is given below:

Step 1:

  • Go to View from the Home
  • Get Macro from the commands.
  • From Macro select the Record Macro.

Step 2:

  • In the Record, the Macro tab gets the Macro name.
  • Give the name of the Macro
  • At the Store macro in the box, we can select the storage location. We will select This Workbook as we want the macro for that sheet in every case.
  • We can add any description to the description box.
  • Then press OK and a macro will create as per the given name.

Step 3:

  • We will enter VBA by pressing Alt+F11.
  • Then click on Insert.

Step 4:

  • Select Module from the Insert options.
  • And get a new window to write code.

Step 5:

  • After completing the code click Run from the main tab.
  • Or click F5 to run the code.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Methods to Use Macro in Insert Rows in Excel

Here, we will create different Macros to insert rows in Excel for different conditions.

1. Insert Single Row Using Macro in Excel

In this section, we will insert a single row using Macro.

1.1 Use of Range Object by Cell Address

Step 1:

  • We will insert a row in this data set.

Insert Single Row Using Macro in Excel

Step 2:

  • Set the Macro name as Insert_single_row.
  • Then press OK.

Insert Single Row Using Macro in Excel

Step 3:

  • Go to the VBA module window by pressing Alt+F11.
  • Write the below code shown on the image.
  • In the code, we used B5 as we wanted to insert a new row on that position.
Sub Insert_single_row()
Range("B5").EntireRow.Insert
End Sub

Step 4:
  • Press F5 to run the code.

 

We can see that a new row is added to Cell B5.


1.2 Use of Range Object

Step 5:

  • We can add a new row by row number also.
  • Type the below code.
  • We used 8:8 as we wanted to add a new row in 8th only.
Sub Insert_single_row()
Range("8:8").EntireRow.Insert
End Sub

Insert Single Row Using Macro in Excel
Step 6:

  • Now, press F5 to run.

We see that a new row is added at the 8th row.


1.3 Use of Row Object

We can also insert by using row numbers in Macro.

Step 7:

  • Modify the previous code. The new code will be like below:
Sub Insert_single_row()
Rows(10).Insert
End Sub

Insert Single Row Using Macro in Excel
Step 8:

  • Now, press F5 to run.

Here, one new row will be inserted at row 10.

Read More: How to Insert Row in Excel ( 5 Methods)


2. Insert Multiple Rows by Macro in Excel

In this section, we will insert multiple rows using Macro.

2.1 Use of Range Object by Cell Address

Step 1:

  • Set the Macro name as Insert_multiple_rows.
  • Then press OK.

Insert Multiple Rows by Macro in Excel

Step 2:

  • Go to the VBA module window by pressing Alt+F11.
  • Write the below code shown on the image.
  • In the code, we used B5:B7 as we want to insert new rows from that range.
Sub Insert_multiple_rows()
Range("B5:B7").EntireRow.Insert
End Sub

Insert Multiple Rows by Macro in Excel

 

Step 3:

  • Press F5 to run the code.

We can see that new rows are added from Cell B5.


2.2 Use of Range Object

Step 5:

  • We can add a new row by row number also.
  • Type the below code.
  • We used 9:10 as we wanted to add new rows in the 9 and 10-row positions.
Sub Insert_multiple_rows()
Range("9:10").EntireRow.Insert
End Sub

Insert Multiple Rows by Macro in Excel

Step 6:

  • Now, press F5 to run.

We see that new rows are added.

Read More: How to Insert Multiple Rows in Excel (6 Easy Methods)


3. Insert Multiple Rows Using Variables in Excel

Step 1:

  • First named the Macro as Insert_rows_by_variable.
  • Then press OK.

Insert Multiple Rows Using Variables in Excel

Step 2:

  • Now, go to the VBA module command window.
  • Write the below code.
Sub Insert_rows_by_variable()
Dim NRow As Long
Dim NCount As Long
Dim N As Long
NCount = InputBox(Prompt:="Number of rows will add")
NRow = InputBox(Prompt:="New rows will be added after?")
For N = 1 To NCount
    Rows(NRow).EntireRow.Insert
Next N
End Sub

Insert Multiple Rows Using Variables in Excel
Step 3:

  • Now, press F5 to run.
  • We will get a new window to provide input.

Step 4:

  • Input 2 and press OK.

Step 5:

  • Again, we get a window to provide input.

Insert Multiple Rows Using Variables in Excel

Step 6:

  • Input 6 here.

Step 7:

  • Now, press F5 to run.Insert Multiple Rows Using Variables in Excel

Read More: How to Insert Multiple Rows After Every Other Row in Excel


4. Insert Rows With Formatting

In this section, we will insert rows with the format. For this, we colored rows 5 and 7, so that we can understand them easily.

Insert Rows With Formatting

Step 1:

  • Now, named the macro as Insert_row_with_format.
  • Then press OK.

Insert Rows With Formatting

Step 2:

  • Now, go to the command window of VBA shown previously.
  • Type the following code.
Sub Insert_row_without_format()
Rows(6).Insert , xlFormatFromLeftOrAbove
End Sub


Step 3:

  • Now, press F5 to run.

Here, we see that in row 6 one row is inserted with the above row format.

Step 4:

  • Again, enter the command module.
  • Type the below code.
Sub Insert_row_without_format()
Rows(8).Insert , xlFormatFromRightOrBelow
End Sub

Insert Rows With Formatting
Step 5:

  • Now, press F5 to run.

Insert Rows With Formatting


Similar Readings


5. Insert Row Without Formatting

First, color row 7 to see that we can insert rows without formatting.

Insert Row Without Formatting

Step 1:

  • Named the Macro as Insert_row_without_formate.
  • Then press OK.

Step 2:

  • Write the below code on the VBA command window.
Sub Insert_row_without_formate()
Rows(6).EntireRow.Insert
Rows(6).ClearFormats
End Sub

Insert Row Without Formatting

Step 3:

  • Now, press F5 to run.

Insert Row Without Formatting

We can see that on row 6 a new insert with no format.


6. Insert Copied Rows by Macro

In this section, we will copy and insert that into our sheet.

Step 1:

  • This is our main data set.
  • We will copy row 2 and insert it on row 10.

 

Insert Copied Rows by Macro

Step 2:

  • Named the Macro as Insert_copied_row.
  • Then press OK.

Step 3:

  • Go to the command window of VBA.
  • Write the following code.
Sub Insert_copied_row()
Range("2:2").Copy
Range("10:10").Insert
End Sub

Insert Copied Rows by Macro
Step 4:

  • Press F5 to run the code.

Insert Copied Rows by Macro

We see that row 2 is copied and inserted at row 10.

Read More: Macro to Insert Row and Copy Formula in Excel (2 Methods)


7. Apply Active Cell Property in Macro

We can insert a new row by active cell property. In this method, a new row will be inserted at the active cell position.

Step 1:

  • Our active cell is B6.
  • The new row will be inserted here.

Apply Active Cell Property in Macro

Step 2:

  • Named the macro as Active_cell_property.
  • Then press OK.

Step 3:

  • Go to the command module of VBA.
  • Write the following code.
Sub Active_cell_property()
  ActiveCell.EntireRow.Insert
End Sub

Apply Active Cell Property in Macro
Step 4:

  • Press F5 to run the code.

Apply Active Cell Property in Macro

We see that a new row is added at the active cell position.


8. Active Cell Property with Offset Function in Excel

Step 1:

  • B4 is our active cell in the data set.

Active Cell Property with Offset Function in Excel

Step 2:

  • Set name for Macro as Active_cell_offset.
  • Then press OK.

Step 3:

  • Go to the command window of VBA.
  • Write the below code.
Sub Active_cell_offset()
ActiveCell.Offset(3).EntireRow.Insert Shift:=xlShiftDown
End Sub

Active Cell Property with Offset Function in Excel

Step 4:

  • Press F5 to run the code.

Active Cell Property with Offset Function in Excel

Read More: VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)


Conclusion

We have shown Excel Macro to insert rows here. We described 8 methods. Hope this will help you to get a solution to your needs. Please give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo