Insert a row
How to insert a single row in a worksheet using Excel, VBA and Shortcut methods
1. Select an entire row where you want to insert a new row. Note: in this example we are inserting a new row as a second row. To select an entire row, click either on the row heading number or select the first cell of the row, press and hold the Ctrl and Shift keys and press the Right key. |
2. Right-click anywhere on the selected row and click Insert. |
METHOD 2. Insert a row using the ribbon option
EXCEL
1. Select any cell in the same row where you want to insert a new row. Note: in this example a new row will be inserted as row 2, given we have selected a cell in the second row. |
2. Select the Home tab. |
3. Click Insert in the Cells group. 4. Click Insert Sheet Rows. |
METHOD 3. Insert a row using the cell option
EXCEL
1. Right-click on any cell in the row where you want to insert a new row. 2. Click Insert Note: in this example a new row will be inserted as row 2, given we have right-clicked on a cell in the second row. |
3. Select the Entire row option and click OK group. |
Worksheets("Sheet1").Range("A2").EntireRow.Insert
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
ADJUSTABLE PARAMETERS
Row Selection: Select where you want to insert a new row by changing the row number in the cell reference ("A2"). You can also change the column reference to any column as this will have no impact on where the row will be inserted.
Worksheet Selection: Select the worksheet where you want to insert a new row by changing the Sheet1 worksheet name.
Worksheets("Sheet1").Range("A2:D2").EntireRow.Insert
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
ADJUSTABLE PARAMETERS
Row Selection: Select where you want to insert a new row by changing the row number reference ("A2:D2"). You can also change the column reference to any column as this will have no impact on where the row will be inserted.
Worksheet Selection: Select the worksheet where you want to insert a new row by changing the Sheet1 worksheet name.
METHOD 3. Insert a row using VBA by selecting an entire row
VBA
Worksheets("Sheet1").Range("2:2").EntireRow.Insert
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
ADJUSTABLE PARAMETERS
Row Selection: Select where you want to insert a new row by changing the row number reference ("2:2").
Worksheet Selection: Select the worksheet where you want to insert a new row by changing the Sheet1 worksheet name.
Method 1
Method 2
NOTES
The Plus Sign key in the first method refers to the key on the top of the keyboard. The Plus Sign key in the second method refers to the key to the right of the keyboard, which some devices will not have. The reason why the first method requires the use of the Shift key is because the Plus Sign key is used for both Plus and Equal Signs, therefore, to activate the Plus Sign you are required to use the Shift key.
This tutorial explains and provides step by step instructions on how to insert a single row in a worksheet using Excel, VBA and Shortcut methods.
Excel Methods: Using Excel you can insert a new row by selecting an entire row or a single cell and using a ribbon or cell option.
VBA Methods: Using VBA you can insert a new row by referencing to a single cell, a range of cells or an entire row. If you want to insert a single row by referencing to a range of cells you need to ensure that the range of cells only references to a single row. As per the example in this tutorial the VBA code references to only row 2 across multiple columns ("A2:D2").
Shortcut Methods: Using a Shortcut you can instantly insert a new row by selecting the entire row where you want to insert a new row and actioning the shortcut.
ADDITIONAL NOTES
Note 1: Inserting a new row will move the existing rows that are below the new row downward. In this this tutorial every row below row 2 will be moved down.
Note 2: If the last row in a worksheet has a cell that is not empty, you will not be able to insert a new row. To insert a row you will need to ensure that every cell in the last row is clear of any content.
Related Topic | Description | Related Topic and Description |
---|---|---|
Insert a column | How to insert a single column in a worksheet using Excel, VBA and Shortcut methods | |
Delete a column | How to delete a single column in a worksheet using Excel, VBA and Shortcut methods | |
Delete multiple columns | How to delete multiple columns in a worksheet using Excel, VBA and Shortcut methods | |
Insert multiple columns | How to insert multiple columns in a worksheet using Excel, VBA and Shortcut methods | |
Insert multiple rows | How to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods |