Excel VBA code to insert a row in between

insert-row

Why insert row through VBA?

There are many situations you need a new row at the end or in between for adding new data into an existing one. But this macro is a waste if you use only for inserting a row because you can insert a new row much easily through excel's traditional way by right clicking on the particular row and select Insert.

This code makes sense when you have huge data you want a new row to be dynamically inserted then this code has a significant role.

What does this code do?

The main objective of this code is to search for a particular word in a column and insert a row above or below. suppose say we have a name list and if you want to insert new name before or after the person "Martin" this code searches for the word "Martin" in a particular column and inserts new name before and after. Let us see in more detail.

Learing outcome - Find a word in a particular column, how to insert row.

insert-row-excel-vba

For example, we are going to target the person named "Martin" and insert a row above for "Daniels" and insert a row below for "Cooper".Let us see the code.

To insert a row above:

First declaring required variables is good practice in VBA as already said earlier it reduces execution time and resources. secondly, we find for the person "Martin" using the code ActiveCell.EntireColumn.Find("Martin").Row and save the row number in the variable n. after finding the row number it is easy to add row before Martin using - Cells(n, 1).EntireRow.Insert. syntax for cells is cells(row index,column index). once the row is inserted we add a new person "Daniels".

Option Explicit
Sub insertbefore()
Dim n As Integer
Range("A1").Activate
n = ActiveCell.EntireColumn.Find("Martin").Row
Cells(n, 1).EntireRow.Insert
Cells(n, 1).Value = "Daniel"
End Sub
Output:
insert-row-excel-vba

As you can see the above code searched for the person "Martin" and inserted a row above that.

To insert a row below:

This is the same as inserting above, we find for the person "Martin" using the code ActiveCell.EntireColumn.Find("Martin").Row and save the row number in the variable n. after finding the row number it is easy to add row after Martin using - Cells(n+1, 1).EntireRow.Insert. syntax for cells is cells(row index,column index).The key point to be noted here is we add +1 to the variable n inside the Cells(n+1, 1).EntireRow.Insert. Once the row is inserted we add a new person "Cooper".

Option Explicit
Sub insertbefore()
Dim n As Integer
Range("A1").Activate
n = ActiveCell.EntireColumn.Find("Martin").Row
Cells(n + 1, 1).EntireRow.Insert
Cells(n + 1, 1).Value = "Cooper"
End Sub
Output
insert-row-excel-vba

As you can see the above code searched for the person "Martin" and inserted a row below that with name "Cooper".

Hope you liked this article, if so please share it with your friends.

This project is available for download in the link below,Try experimenting with this code to get clarity.

Excel Logo

Download project file

If you want more clarity or demo of this code, please watch the below youtube video.

Detailed Video: Insert Row Before or After Using VBA

Share on:
Most Popular