How to: Add a New Row or Column to a Worksheet

• Dec 09, 2022
• 3 minutes to read

Row

This example demonstrates how to insert new rows into a worksheet.

• Use the Row.Insert method to add a new row above the current row.
• Call the RowCollection.Insert method of the Worksheet.Rows collection to insert a row at the specified position or add multiple rows at once.
• To insert empty rows above the specified cell range, use the Worksheet.InsertCells method with the InsertCellsMode.EntireRow enumeration member passed as a parameter. This method inserts the same number of rows as the specified cell range contains.

Enclose your code in the SpreadsheetControl.BeginUpdate - SpreadsheetControl.EndUpdate method calls to suppress the SpreadsheetControl’s visual updates and improve its performance when you add numerous rows to a document.

View Example

RowAndColumnActions.cs

// Insert a new row 3.
worksheet.Rows["3"].Insert();

// Insert a new row into the worksheet at the fifth position.
worksheet.Rows.Insert(4);

// Insert five rows into the worksheet at the ninth position.
worksheet.Rows.Insert(8, 5);

// Insert two rows above the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range["L15:M16"], InsertCellsMode.EntireRow);

RowAndColumnActions.vb

' Insert a new row 3.
worksheet.Rows("3").Insert()

' Insert a new row into the worksheet at the fifth position.
worksheet.Rows.Insert(4)

' Insert five rows into the worksheet at the ninth position.
worksheet.Rows.Insert(8, 5)

' Insert two rows above the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range("L15:M16"), InsertCellsMode.EntireRow)

Note

The number of rows in a worksheet is permanently fixed - 1,048,576.

After new rows have been inserted into a worksheet via the control’s UI, the SpreadsheetControl.RowsInserted event is raised.

Column

This example demonstrates how to insert new columns into a worksheet.

• Use the Column.Insert method to add a new column to the left of the current column.
• Call the ColumnCollection.Insert method of the Worksheet.Columns collection to insert a column at the specified position or add multiple columns at once.
• To insert empty columns to the left of the specified cell range, use the Worksheet.InsertCells method with the InsertCellsMode.EntireColumn enumeration member passed as a parameter. This method inserts the same number of columns as the specified cell range contains.

Enclose your code in the SpreadsheetControl.BeginUpdate - SpreadsheetControl.EndUpdate method calls to suppress the SpreadsheetControl’s visual updates and improve its performance when you add numerous columns to a document.

View Example

RowAndColumnActions.cs

// Insert a new column C.
worksheet.Columns["C"].Insert();

// Insert a new column into the worksheet at the fifth position.
worksheet.Columns.Insert(4);

// Insert three columns into the worksheet at the seventh position.
worksheet.Columns.Insert(6, 3);

// Insert two columns to the left of the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range["L15:M16"], InsertCellsMode.EntireColumn);

RowAndColumnActions.vb

' Insert a new column C.
worksheet.Columns("C").Insert()

' Insert a new column into the worksheet at the fifth position.
worksheet.Columns.Insert(4)

' Insert three columns into the worksheet at the seventh position.
worksheet.Columns.Insert(6, 3)

' Insert two columns to the left of the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range("L15:M16"), InsertCellsMode.EntireColumn)

Note

The number of columns in a worksheet is permanently fixed - 16,384.

After new columns have been inserted into a worksheet via the control’s UI, the SpreadsheetControl.ColumnsInserted event is raised.