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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

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


Related pages


macro remove blank rowsinteractive excel dashboardsaccess vba run queryvba formscombining excel worksheetsvlookup refhow to hide columns on excelinserting check boxes in excelwhat is vba macrosvba userform codelearn excel for beginnerscell vba excelvba activeworkbookmultiple if statements excel 2010excel macro referencelock excel spreadsheetcan you unprotect an excel spreadsheetthisworkbook vbavb6 ado exampleexcel vba unhide all sheetsmacro for beginnershow to use iferror function in excel 2010excel beginnershow to change the width of a cell in exceldefinition of filtering in excelaccess vba insert into table from formdefinition of excel formulasapplication.getopenfilenameexcel using macroshow to protect sheet in excelhow to use sumif in excelexcel formulas vlookupcombo box in visual basicvba unprotect workbookunlock password protected excel sheetexcel vba programming examplesmeeting templates excelwhat is the purpose of vlookup function in excelremoving duplicate data in excelpowerpoint swot analysis templateruntime 1004 vbaexcel formula sum ifend vbaexcel vba bordervba script excelhow to highlight a column in excelmultiple if statements excelvba excel remove duplicatesmainframe sas tutorialinteractive dashboard excelmerge multiple excel worksheets into oneexcel if statement examples texthow to delete defined names in excelexcel unhide all cellsmacro for vlookupvba clear stringcreate checkbox in excelnutshell tutorialvba application displayalertsvisual basic calculator tutorialxls macro programmingexcel shortcutawesome excel templatescreate check box in excelapplication statusbarworkbook.openexcel match typevba excel programshow to use averageifsvba funtion