VBA Insert Range in Excel

We can use VBA to Insert a cell or range(Collection of cells) in a worksheet, While inserting a range it will shifts the other cells towards Right(xlToRight) or Down(xlDown) side. VBA Insert range will be helpful while automating a task and you want to provide some more cells in between the another range. You can also insert entire Row(s) or Column(s) in that particular range.

VBA Insert Cell or Range in a worksheet – Syntax

Here is the syntax to Insert a cell or range(Collection of cells) in a worksheet and then it will shifts the other cells towards Right(xlToRight) or Down(xlDown).

Range(“YourRange”).Insert(

[Shift], [CopyOrigin])

Here,

Shift – (Optional): It will specify the cells to which side has to shift. Following are the two constants xlToRight and xlDownto shift the cells to either right or down side.
CopyOrigin – (Optional): we can use the CopyOrigin to mention destination location of the range.

VBA Insert Range in a Worksheet – xlDown

Below is the Excel VBA Macro or code to Insert range.Here inserting the range in “C7” and moving the cells towards down position.

Sub Insert_Range_xlDown()
  
    Range("C7").Insert Shift:=xlDown

End Sub

VBA Insert Range in a Worksheet – xlToRight

Below is the Excel VBA Macro or code to Insert range.Here inserting the range in “C7” and moving the cells towards right side position.

Sub Insert_Range_xlToRight()

    Range("C7").Insert Shift:=xlToRight

End Sub

VBA Insert Range in a Worksheet – EntireRow

Below is the Excel VBA Macro or code to Insert entire rows in the range.Here we are inserting rows in the range(“B2:D10”).ie. This will insert the new rows 2 to 10.

Sub Insert_Range_EntireRow()

    Range("B2:D10").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Here CopyOrigin tells excel weather if you want to insert the new rows with the above or below row formats. ‘xlFormatFromLeftOrAbove’ will copy the above rows formats while inserting new rows. xlFormatFromRightOrBelow will copy the formats from the below rows while inserting new rows.

VBA Insert Range in a Worksheet – EntireColumn

Below is the Excel VBA Macro or code to Insert entire columns in the range.Here we are inserting columns in the range(“B2:D10”).ie. This will insert the new columns ‘B’ to ‘D’.

Sub Insert_Range_EntireColumn()

    Range("B2:D10").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Here CopyOrigin tells excel weather if you want to insert the new columns with the left or right column formats. ‘xlFormatFromLeftOrAbove’ will copy the left side column formats while inserting new columns. xlFormatFromRightOrBelow will copy the formats from the right side column while inserting new columns.

VBA Insert Range in a Worksheet – Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

1. Open an Excel Workbook from your start menu or type Excel in your run command
2. Enter some data in any cells in range “C5:E6″ to test this macro.
3. Press Alt+F11 to Open VBA Editor or you can go to Developer Tab from Excel Ribbon and click on the Visual Basic Command to launch the VBA Editor
4. Insert a Module from Insert Menu of VBA
5. Copy the above code (for copying a range using VBA) and Paste in the code window(VBA Editor)
6. Save the file as Macro Enabled Workbook (i.e; .xlsm file format)
7. Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line.