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


dcl commandscosting templatevba code functionlookup excel 2007how to do stacked bar chart in excel 2010excel schedule plannerexcel vba option button valuevba progress bar excelhow to apply validation in excelexcel 2003 vlookuphighlight duplicate entries in excelvba create drop down listexcel vba codesort formula in excelunlock excel filedashboard using excelpractice pivot tableshow to check duplicate records in excelexcel formulas offsetexcel loopingapplication.vlookupunhide column a in excel 2013how to make a pivot table in excel 2007opening xlsm filesvba delete columnopen xlsx with excel 2003listbox excel vbaworkbook activatesql coding interview questionsmacro to close excel filerecord macro excel 2007vba copy data from one workbook to anotherexcel formula templatesmacros on excelwhat is a pivot table excel 2010vba vbokcancelhow do i merge cells in excelexecute sql query from excelsumif formulasexcel months between two datesvba string containsremove empty cells excelpivot table 2013 tutorialhow to work on pivot table in excel 2010vba vbnewlinesas sql trainingcountif excel vbaexcel spreadsheet gantt chart templatemsgbox syntax in vbvba rename filesexcel hidden columnscopy data from multiple worksheets into oneexcel insert row with formulaexcel fx functionexcel find last cell with datahow to learn vba in excelpowerpoint meeting agenda templatepowerpoint project status dashboard templatehow to lock an excel fileexcel unshare workbookindex formula excel 2007activecell.columnhow to unhide worksheets in excelexcel vba worksheet changehow to use advanced filter in excelpivot table explanationexcel hyperlink to macroexcel dashboard graphscopy excel sheet to another workbookvba row countvba loop statementhow to use a pivot table excel 2010array function in vbahow to remove duplicate numbers in excelhow to create pivot table in excel 2007