VBA Insert Row or Column

Associated Files Download Links

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:

Or you can use the Range Object along with EntireRow:

Insert New Column

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

Or the Range Object, along with EntireColumn:

Insert Multiple Rows or Columns

Insert Multiple Rows

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

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

Insert Multiple Columns

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

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

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 right.

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:

vba insert row

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

vba insert row above

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

vba insert row below

Other Insert Examples

Insert Copied Rows or Columns

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

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:

Delete Rows or Columns

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