How To Add A New Row In A Table To Add New Data In Excel Vba

Return to VBA Code Examples

VBA Insert Row or Column

In this Article

Insert a Single Row or Column
- Insert New Row
- Insert New Column
Insert Multiple Rows or Columns
- Insert Multiple Rows
- Insert Multiple Columns
Insert – Shift & CopyOrigin
Other Insert Examples
- Insert Copied Rows or Columns
- Insert Rows Based on Cell Value
- Delete Rows or Columns

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

To insert rows or columns we volition utilise the Insert Method.

Insert a Single Row or Cavalcade

Insert New Row

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

Or you tin can use the Range Object along with EntireRow:

Range ( "b4" ) . EntireRow . Insert

Insert New Column

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

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:

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

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

Insert Multiple Columns

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

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

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

VBA Coding Made Easy

Stop searching for VBA lawmaking online. Learn more most AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding cognition and with many fourth dimension-saving features for all users!

Learn More!!

Insert – Shift & CopyOrigin

The Insert Method has two optional arguments:

Shift – Which management to shift the cells
CopyOrigin – Which cell formatting to re-create (to a higher place, beneath, left, or right)

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

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

Equally you tin encounter, you tin't shift up or to the left.

The CopyOrigin argument has 2 potential inputs:

xlFormatFromLeftorAbove – (0) Newly-inserted cells accept formatting from cells above or to the left
xlFormatFromRightorBelow (one) Newly-inserted cells have formatting from cells below or to the correct.

Permit's look at some examples of the CopyOrigin argument. Hither's our initial information:

vba insert row

This example volition insert a row, taking the formatting from the higher up row.

Rows ( 5 ) . Insert , xlFormatFromLeftOrAbove

vba insert row above

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

Rows ( 5 ) . Insert , xlFormatFromRightOrBelow

vba insert row below

Other Insert Examples

Insert Copied Rows or Columns

If you'd like to insert a copied row, you lot would use lawmaking like this:

Range ( "one:1" ) . Copy

Range ( "v:5" ) . Insert

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

VBA Programming | Lawmaking Generator does work for you!

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" So

< span data-story-id="story_9ba7732072e0f3f2544fa9b74ecce5ad" data-room-id="room_ae564c9c36d1afd4cf6b60ee82c7b744" information-timestamp="1649525064822" data-text="cell.Offset(1).EntireRow.Insert" data-userid="923153038361395200" data-orgid="923153038361395202" > jail cell . Offset ( 1 ) . EntireRow . Insert </span >

Finish If

Side by side cell

End Sub

Delete Rows or Columns

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

Rows ( one ) . Delete

Range ( "a1" ) . EntireRow . Delete

Columns ( i ) . Delete

Range ( "a1" ) . EntireColumn . Delete