VBA Insert Row or Column

This tutorial will demonstrate how to use VBA to insert rows and columns in Excel.

To insert rows or columns we will use the Insert Method.

Insert a Single Row or Column

Insert New Row

To insert a single row, you can use the Rows Object:

Rows(4).Insert

Or you can use the Range Object along with EntireRow:

Range("b4").EntireRow.Insert

Insert New Column

Similar to inserting rows, we can use the Columns Object to insert a column:

Columns(4).Insert

Or the Range Object, along with EntireColumn:

Range("b4").EntireColumn.Insert

Insert Multiple Rows or Columns

Insert Multiple Rows

When inserting multiple rows with the Rows Object, you must enter the rows in quotations:

Rows("4:6").Insert

Inserting multiple rows with the Range Object works the same as with a single row:

Range("b4:b6").EntireRow.Insert

Insert Multiple Columns

When inserting multiple columns with the Columns Object, enter the column letters in quotations:

Columns("B:D").Insert

Inserting multiple columns with the Range Object works the same as with a single column:

Range("b4:d4").EntireColumn.Insert

Insert – Shift & CopyOrigin

The Insert Method has two optional arguments:

• Shift – Which direction to shift the cells
• CopyOrigin – Which cell formatting to copy (above, below, left, or right)

The Shift argument is irrelevant when inserting entire rows or columns. It only allows you to indicate to shift down or shift to the right:

• xlShiftDown – Shift cells down
• xlShiftToRight – Shift cells to the right

As you can see, you can’t shift up or to the left.

The CopyOrigin argument has two potential inputs:

• xlFormatFromLeftorAbove – (0) Newly-inserted cells take formatting from cells above or to the left
• xlFormatFromRightorBelow (1) Newly-inserted cells take formatting from cells below or to the right.

Let’s look at some examples of the CopyOrigin argument. Here’s our initial data:

This example will insert a row, taking the formatting from the above row.

Rows(5).Insert , xlFormatFromLeftOrAbove

This example will insert a row, taking the formatting from the below row.

Rows(5).Insert , xlFormatFromRightOrBelow

Other Insert Examples

Insert Copied Rows or Columns

If you’d like to insert a copied row, you would use code like this:

Range("1:1").Copy
Range("5:5").Insert

Here we copy Row 1 and Insert it at Row 5.

Insert Rows Based on Cell Value

This will loop through a range, inserting rows based on cell values:

Sub InsertRowswithSpecificValue()
 
Dim cell As Range
 
For Each cell In Range("b2:b20")
    If cell.Value = "insert" Then
        cell.Offset(1).EntireRow.Insert
    End If
Next cell
 
End Sub

Delete Rows or Columns

To delete rows or columns, simply use the Delete method.

Rows(1).Delete

Range("a1").EntireRow.Delete

Columns(1).Delete

Range("a1").EntireColumn.Delete