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.
Also Read: Insert Columns using VBA in Excel
- 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.
Also Read: Cells in VBA – Usage With Examples
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.❤