Insert Rows using VBA, Copy Formatting

Today, we are going to learn an important concept of adding rows to the worksheet using VBA. We will learn to insert a single row and multiple rows. We will learn to copy the row formatting from the previous or next row.

You must know the use of command buttons as we have implemented the Insertion of rows using Command Buttons.

So let us start learning.

Inserting Rows using VBA

We can use the Insert method with the Range or Rows object to refer to the row before or after which we wish to insert a new row. There are two ways to insert rows using VBA. We can either refer to a single cell using Range and then use the EntireRow property to insert a row before it. Alternatively, we can also use the Rows function and refer to the row using numbers to add a new row before the specified row number.

We will learn everything with the help of examples to avoid any kind of confusion. At the end of the article, you will understand everything.

You can use the following practice workbook to follow along with us.

Insert Method Arguments – Insert Rows

We use the Insert method to insert rows using VBA in our spreadsheet. It has two optional arguments and if we do not specify them then their default values are used.

Insert ([Shift],[CopyOrigin])

• Shift – This argument has only one value for inserting rows. It is irrelevant as we cannot shift cells up using the argument.
  • xlShiftDown – This inserts a new row after the specified row.
• CopyOrigin – We can specify from where we wish to copy the formatting of the newly inserted row. It can have two values.
  • xlFormatFromLeftOrAbove – (0) This is the default value and copies the row formatting from the top row.
  • xlFormatFromRightOrBelow – (1) This copies the row formatting from the bottom row.

Insert a Single Row Before the Specified Cell

We refer to a single cell in VBA using the Range property. The EntireRow property lets us access the complete row belonging to the cell specified in the Range. Thereafter, we use the Insert method to insert a new row before the specified row.

Let us say I wish to add a row above cell C3.

We will use a command button and then code would be executed when the button is clicked. Insert a command button from the Developer tab and enter the following code in the click event of the command button.

Sub Button1_Click()
'insert a row before row containing cell C3
Range("C3").EntireRow.Insert
End Sub

When you click on the command button, this code executes and we see the result.

Insert a Single Row Before the Active Cell

In this example, we would learn to insert a single row before the Active Cell in the spreadsheet.

We can use the following code along with the command button click and it will insert a new row based on where we are pointing at.

Sub SingleRowActiveCell_Button1_Click()
'insert a row before active cell
ActiveCell.EntireRow.Insert
End Sub

When you click on the command button, it inserts a new row before the active cell.

Inserting Multiple Rows using Range Property

We can insert multiple rows before a row by using the Range specified in the Range property. We will take input from the user that how many rows it wants to add before the active cell and then add them using the for loop counter variable.

Use the following code to insert a number of rows before the active cell.

Here we have applied a condition check that the number of rows must not be negative or greater than 10 rows.

Sub MultipleRowsbeforeActiveCel_Button1_Click()
'insert multiple rows before active rows
Dim new_rows As Integer
'ask from user the number of rows he wishes to insert
new_rows = InputBox("Enter number of rows")
'apply the condition check for validity of value
If new_rows > 0 And new_rows < 10 Then
    'i is counter variable
    Dim i As Integer
    For i = 1 To new_rows
    ActiveCell.EntireRow.Insert
    Next i
End If
End Sub

Copy Formatting From Top and Bottom Row

We can copy the formatting from the top row or bottom row with the help of the CopyOrigin Argument of Insert method.

Use this code to insert a new row and copy the formatting from the top row.

Sub CopyFormatringTopRow_Button1_Click()
ActiveCell.EntireRow.Insert 0
End Sub

Similarly, we can copy the formatting from the bottom row by taking the CopyOrigin Argument as 1.

Sub CopyFormatringBottomRow_Button1_Click()
ActiveCell.EntireRow.Insert , 1
End Sub

This brings us to an end.

Thank you for reading.❤