Use the Range.Insert method to insert a row into a worksheet. Newly-inserted cells take the formatting from cells above or to the left. Default: xlFormatFromLeftOrAbove or 0.xlFormatFromRightOrBelow or 1: Newly-inserted cells take formatting from cells below or to the right.xlFormatFromLeftOrAbove or 0: Newly-inserted cells take formatting from cells above or to the left.Values: A constant from the xlInsertFormatOrigin Enumeration:.Description: Specifies from where (the origin) is the format for the cells in the newly inserted row copied.Usage notes: When you insert a row: (i) use xlShiftDown or -4121, or (ii) omit parameter and rely on the default behavior.Default: Excel decides based on the range's shape.

xlShiftToRight or -4161: Shifts cells to the right.

xlShiftDown or -4121: Shifts cells down.Values: Use a constant from the xlInsertShiftDirection Enumeration:.Description: Specifies the direction in which cells are shifted away to make space for the newly-inserted row.To make space for the newly-inserted cells, Range.Insert shifts other cells away.For purposes of this VBA Tutorial, you're interested in inserting entire rows. Range.Insert can insert a single cell or a cell range.The 2 main characteristics of the Range.Insert method are the following: Use the Range.Insert method to insert a cell range into a worksheet. Excel VBA Constructs to Insert Rows Insert Rows with the Range.Insert Method Purpose of Range.Insert You can use the VBA constructs and structures I describe below to automate this process to achieve a variety of results. Use the “Ctrl + Shift + +” keyboard shortcut.Go to Home > Insert > Insert Sheet Rows.Select the row or rows above which to insert the row or rows.When working manually with Excel, you can insert rows in the following 2 steps: Example #9: Excel VBA Insert a Number of Rows Every Number of Rows in a Data Range.Example #8: Excel VBA Insert Blank Rows Between Rows in a Data Range.Example #7: Excel VBA Insert Copied Row.Example #6: Excel VBA Insert Row Below Active Cell.Example #5: Excel VBA Insert Row without Formatting.Example #4: Excel VBA Insert Row with Same Format as Row Below.Example #3: Excel VBA Insert Row with Same Format as Row Above.Example #2: Excel VBA Insert Multiple Rows.Clear Row Formatting with the Range.ClearFormats Method.Specify Entire Row with the Range.EntireRow Property.Specify a Cell Range a Specific Number of Rows Below or Above a Cell or Cell Range with the Range.Offset Property.Specify a Cell with the Worksheet.Cells and Range.Item Properties.Specify a Cell Range with the Worksheet.Range Property.Specify the Active Cell with the Application.ActiveCell Property.Specify Rows with the Worksheet.Rows Property.Insert Rows with the Range.Insert Method.

Insert rows in excel