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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


VBA Insert Method Excel Range Object

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.
LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource
Excel VBA Reference Project Management Reference
VBA Reference: Excel Reference:
External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference: Excel Reference:

One Comment

  1. Yrjö Seppänen May 19, 2016 at 3:18 PM - Reply

    How can I insert a range of 6 rows (or 6 rows) before every 28th row, i. e. 34., 62., 90., and so on?

Leave A Comment