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


unprotect excel with passwordinsert checkbox excelunprotect excel workbook 2010 without passwordexcel datevalueexcel formula macrovba workbook activatevba excel objectmicrosoft excel templates project managementmerge cells in excel 2010excel check for duplicatevba commands excelsave workbook vbaexcel command buttonsvbyesnodml statements in sql serverhow to lock a tab in excelhow to start vba in excelhow to open excel workbookfind last row vbainserting dropdown in excelhow to protect file in excel with passworddelete rows in excel vbaclustered bar chart excelvlookup with isnaexcel pivotingvba development environmenthow to build excel macrosmacro insert rowunprotect excel sheet 2013excel 2013 average functionvba to remove duplicateswhat is the vlookup functionactivesheet rangedata types vbaexcel formulas sumifexcel usedrangeexcel 2010 advanced filtersimple if statement excelmysql adodb connection stringhow to sort excel sheets alphabeticallyvb6 checkboxvba comment codeexcel vba end xlupmacros basicsconsolidate in excelexcel formula isna vlookupwhat is the shortcut key to hide row in excelhyperlink functionvba fileformatsas interview questions on macrosexcel vba arrayhow to insert row in excel shortcut keyexcel vba saveas fileformathow to protect excel sheet from deletingiserror formula exceltrim functionexcel option explicitlearning vlookup in excel 2007excel dashboard vbacounta vbavbscript check file existsexcel formula for finding duplicatestclsh commandsexcel 2007 listboxms excel row functionswitch case vbaexcel vba delete rowclose file vbavba delete duplicateshow to crack excel macro passwordworksheet function in vbasave excel file vbamacro formula exceluserform propertiesadvance filter exceladodb sql server connection stringhide duplicates in excelmicrosoft word macros examplesexcel 2010 multiple if statementslock sheet excelvba excel torrentmicrosoft word meeting notes template