VBA Range.Insert Method: Code line makes a space to put new range in

[DocAElstein]

4 Examples using demo code Sub SpreadApartSlipInGetColoured()

Make spreadsheet Area Free by moving all cells to the right, and take the formats from the left.
Make Area Free by moving all cells to the right, and take the formats from the first column in the cells before those that were moved to the right
( If it is chosen to Shift cells to the right ( Shift:=xlShiftToRight ) when making a space to insert new cells, then taking formats from the left is the default if that second optional parameter ( CopyOrigin:= __ ) argument is omitted )

Start by selecting a spreadsheet Area. ( This can be changed later )
Start.JPG : https://imgur.com/JxwZAyC
start code Sub SpreadApartSlipInGetColoured

Select to make available an Area for a new virgin range by Shifting all cells to the right. ( The cells Shifted will start from the left most column of that selection Range )
ShiftCellsToRight.JPG : https://imgur.com/0igT0S1

Select and / or confirm the area to be made free to allow a new range top be inserted
SelectConfirmAreaForNewRange.JPG : https://imgur.com/S2PBtPl

Cells are shifted to the right to make space for a new range Area
CellsShiftedToRightToMakeSpaceForNewRangeArea.JPG : https://imgur.com/ZFnTUd7

Note that the Area originally at the selected Area has also Shifted and consequently has another Address. This is important to note as any variable used to hold the selected Area range object, will, on further use now refer to this new address and not the original.
RangeObjectOfSelectedRangeHasChangedToNewAddress.J PG : https://imgur.com/dlHZ3Q7

Chose that the format for the new cells should come from the cells in the first column to the left of the new range. ( This is the default option when previous Shift is to the right ( Shift:=xlShiftToRight ) )
ChooseToCopyFormatsFromLeft.JPG : https://imgur.com/nmTTUoU

The code pauses, just for fun, to show what would be the full area from which to make an identical Format copy. ( This is the adjacent left mirrored range to the selected Area )
FullFormatCopyRange.JPG : https://imgur.com/vQqWcYr

The actual used single column cells which are used for the format copy and paste are shown
SingleColumnFormatCopyRanges.JPG : https://imgur.com/Y6lz6VX

The copied formats are pasted across all columns
PasteFormatsOverFullNewRangeColumns.JPG : https://imgur.com/MGriOm0

It is intended to reverse all the previous actions in preparation of doing the same with the standard _ Range.Insert code line
NewRangeWillBeDeleted.JPG : https://imgur.com/jR59Cv5

After using the _ Range.Delete Method _ with the appropriate “reverse” options to those used for the Shift and Insert steps so far taken, then we obtain the original spreadsheet as was before any steps were taken.
AfterDeleteWeHaveOriginalSpreadsheet.JPG : https://imgur.com/iJg8zH3

It is intended to use the standard _ Range.Insert code line _ using the Options used previously and still held in variables in the code
Range_InsertCodeLineWillBeUsed.JPG : https://imgur.com/utvAjWz

On performing the standard _ Range.Insert code line _ using the Options used previously ( Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromLeftOrAbove ) and still held in variables in the code, the same Shift and insert results are obtained
SameResultsWithStandardRange_InsertCodeLine.JPG : https://imgur.com/N6brUv6

Finally the demo code presents the actual _ Range.Insert code line _ that would be needed to perform the operation of Shifting and inserting discussed and demonstrated
StandardRange_InsertCodeLineDisplayedInMessageBox : https://imgur.com/ZK2gorS

The Range.Insert code line can also be seem in the Immediate window. This can be erected if one uses the key combination of _ Ctrl+g _ when one is in the VB Editor window environment. ( The VB Editor window can be obtained when the key combination of _ Alt+F11 _ is used from the Excel Spreadsheet environment )
ImmediteWindowDisplaysRequired.JPG : https://imgur.com/8RfsGvQ


Moving cells down to put in a range and taking formats from the next row up
Moving cells down to put in a range and taking formats from the row above the inserted range
( When selecting to Shift cells down for enabling a new range to insert, the default is to take the format from the next row up )

Make a start by selecting a range, ( this can be changed later ), _..
Start.JPG : https://imgur.com/7XFxFpz
_...and start code running Sub SpreadApartSlipInGetColoured

Choose to shift cells down:
ChooseShiftAllRowsDown.JPG : https://imgur.com/DzuP9lB

Confirm selection of the spreadsheet area to be made free by shifting cells
SelectConfirmRange.JPG : https://imgur.com/9ihVp6G

The cells are shifted down ( Including original range selected)
NewRangeAndAllCellsBelowShiftDown.JPG : https://imgur.com/yFsuKGY

Chose to take the format from above
SelectToTakeFormatFromAbove.JPG : https://imgur.com/vuH2csX

Initially the range area above with the same dimensions of the area made free is selected by my code , ( just for fun in this code )
FullCopyRange.JPG : https://imgur.com/jmuBgrF

The required area for format copy is however just one row, that is to say, the current working of the Range.Insert Method, would only use one row in such a case )
ReducedCopyRange.JPG : https://imgur.com/L9Za48F

Those single row formats are pasted across all rows of new Area
PasteRowFormatsAcrossAllNewRange.JPG : https://imgur.com/hcTIU1U

_.........................

The above was all intended to demo what actually goes on in a Range.Insert code line.
The code continues to do an actual Range.Insert code line using the options taken in and used in the above.
NewRangeWillBeDeleted.JPG : https://imgur.com/C2PIquA

Initially a delete is done to reverse the actions done above
BackToStartSituaion.JPG : https://imgur.com/oqpOO4B

The standard single Range.Insert code line is done using the options chosen so far, and the command options used is displayed
StandardCodeLineCommand.JPG : https://imgur.com/rWpuNV2

The information can also be copied from the Immediate window if Keys _ Ctrl+g _ are used when the VB Editor ( Alt+F11) is selected
CommandsDisplayedInImmediateWindow.JPG : https://imgur.com/pMV7HOu

_.________________________________________________ _________________

[DocAElstein]

__________________________________________________ ______________

Moving cells down to put in a range and taking formats from the next row down
Moving cells down to put in a range and taking formats from the first of the rows that was shifted

A range is selected where a new range should be inserted. ( This can be changed later )
Start.JPG : https://imgur.com/KEchHgI

Chose to move all cells below the selection (and the cells in the selection ), down to make space for the new range
ShiftDown.JPG : https://imgur.com/wjEYmSN

Select and / or confirm the range wanted where a new range should be inserted
SelectConfirmNewShiftRange.JPG : https://imgur.com/lZMC8l7

The cells below and included in the selected area are shifted down
RangesShiftedDown.JPG : https://imgur.com/vmrSonk

Note the area under the selection was also shifted down and so has a new address
SelectedrangeWasAlsoShifted.JPG : https://imgur.com/dMOjoV5

It is chosen to use the formats from below for the newly inserted range
ChoseFormatsfrombelow.JPG : https://imgur.com/9y96aZh

Just for fun, my code initially shows what would be the range to use to copy formats for an exact copy of the original formats: This is actually the range that had been in the area where the new area has been inserted
FullCopyRangeAlsoShiftedSelectedRange.JPG : https://imgur.com/xkY7fh4

The actual range used for the formats to be copied is a single row
ReducedCopyRange.JPG : https://imgur.com/y9cmc3h

The format from the single row is pasted over all rows of the newly inserted range. In this example it means that all cells in the new range have no formatting
PasteFormatOverWholeNewRange.JPG : https://imgur.com/XugnPt5

All of the last actions will be reversed
LastActionsToBeReversed.JPG : https://imgur.com/CbMBcBt
This is achieved by deleting the new range and shifting “back up” the cells from bellow to give finally the original stand
OriginalWorksheetRangeForInsertSelected.JPG https://imgur.com/Lgu6B14

The options used in the demo process are still stored in variables in the code, and these are used in the standard Range.Insert code lime to give the same final results as from the detailed demo process
ResultFromRange_InsertCommand.JPG : https://imgur.com/czpCYYn

The final Range.Insert command line is shown
CommandLine.JPG : https://imgur.com/NPgHo8M

If one is in the VB Editor, then on using the Keys, Ctrl+g, the Immediate window is seen that also contains the single code line
CommandInImmediateWindow.JPG : https://imgur.com/x56Deht


Make spreadsheet Area Free by moving all cells to the right, and take the formats from the right.
Make Area Free by moving all cells to the right, and take the formats from the first column in the cells from the original range that had been in the selected Area but which are, along with many other cells, Shifted to the right

Start by selecting a spreadsheet Area. ( This can be changed later )
Start.JPG : https://imgur.com/o7AoBL4

Select to make available an Area for a new virgin range by shifting all cells to the right, (starting with the left most column of the selection Range )
SelectSpreadCellsToRight.JPG : https://imgur.com/qEAZSNn

Select and / or confirm the area to be made free to allow a new range top be inserted
SelectConfirmSelection.JPG : https://imgur.com/Eb95hML

Cells are Shifted to the right.
Note that the Area which was originally at the selected Area has also Shifted and consequently has another Address. This is important to note as any variable used to hold the selected Area range object, will on further use now refer to this new address and not the original.
NoteAreaPreviouslyInSelectedAreaHasAlsoMoved.JPG : https://imgur.com/XEqOhLC

Chose that the format for the new cells should come from the cells in the first column of the original range object which was in the selected area, but which is now Shifted to a new address
FormatToComeFromRight.JPG : https://imgur.com/czFBesz

The code pauses, just for fun, to show what would be the full area from which to make an identical Format copy. This is also the new Area held by the range which had the same dimensions of the selected area, and which Shifted along with all other cells to the right
FormatFullCopyArea.JPG : https://imgur.com/kdBlkfW

The actual used single column cells which are used for the format copy and paste are shown
SingleColumnCellsFormatCopyArea.JPG : https://imgur.com/1FStiuk

The copied formats from a single column are pasted across all columns
FormatsPastedAcrossAllColumns.JPG : https://imgur.com/dC20wcE

It is intended to reverse all the previous actions in preparation of doing the same again with the standard _ Range.Insert code line
ActionsWillBeReversed.JPG : https://imgur.com/inWqIuV

After using the _ Range.Delete Method _ with the appropriate “reverse” options to those used for the Shift and Insert steps so far taken, then we obtain the original spreadsheet as was before any steps were taken.
BackToOriginalSpreadsheet.JPG : https://imgur.com/crSbuXz

It is intended to use the standard Range.Insert code line using the Options used previously and still held in variables in the code
Range_InsertWillBeUsedWithSameOptions.JPG : https://imgur.com/SDEKdDX

After performing the standard Range.Insert code line using the options used previously and still held in variables in the code, the same Shift and insert results are obtained
ResultsUsingRange_InsertCodeLine.JPG : https://imgur.com/JEpuEm2

Finally the demo code presents the actual Range.Insert code line that would be needed to perform the operation of Shifting and inserting discussed and demonstrated
Range_InsertCodeLineShownInMessageBox.JPG : https://imgur.com/pWIoExG

The Range.Insert code line can also be seem in the Immediate window. This can be erected if one uses the key combination of _ Ctrl+g _ when one is in the VB Editor window environment. ( The VB Editor window can be obtained when the key combination of _ Alt+F11 _ is used from the Excel Spreadsheet environment )
Range_InsertCodeLineShownInImmediateWindow.JPG : https://imgur.com/jfiOOnl

[DocAElstein]

First Code for last few posts

 ' https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4555023    https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-12.html#post4555457     https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-12.html#post4561126     https://www.excelforum.com/tips-and-tutorials/1136702-vba-range-insert-method-excel-oop-syntax-error.html       https://testosteronesboosterweb.com/titan-blast/
Option Explicit
Dim rngCopy As Range, rngNew As Range
Dim dicLookupTableMSRD As Object
Sub MeOwl()  '        https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822550
' Arbritrary Test Range Clear and Refresh
Rows("1:20").Clear
Dim RngObj1Area As Range
 Set RngObj1Area = Range("B2:J10")
 Let RngObj1Area.Interior.Color = vbYellow
' List of Range.Insert parameter argument options
 Set dicLookupTableMSRD = CreateObject("Scripting.Dictionary") 'Late Binding MSRD   In this case Dictionary and Scripting.Dictionary are the same. You can be sure of that because removing the reference to the Scripting runtime makes the Dictionary code fail. When you declare a variable as Dictionary, the compiler will check the available references to locate the correct object. There is no native VBA.Dictionary incidentally, though it is of course possible to create your own class called Dictionary, which is why I used the phrase "in this case".    https://www.excelforum.com/excel-programming-vba-macros/1147313-dictionary-or-scripting-dictionary-binding-referencing-dim-ing-sub-routines-and-function.html#post4431231   http://www.eileenslounge.com/viewtopic.php?f=30&t=24955#p193413        https://www.excelforum.com/excel-programming-vba-macros/1135724-compare-and-find-missing-rows-in-two-sheets-with-different-headings-and-formats.html       http://advisorwellness.com/blue-fortera/
 Let dicLookupTableMSRD.CompareMode = vbTextCompare
 dicLookupTableMSRD.Add Key:=-4121, Item:="xlShiftDown or -4121: Shifts cells down." '  XlInsertShiftDirection   https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method
 dicLookupTableMSRD.Add Key:=-4161, Item:="xlShiftToRight or -4161: Shifts cells to the right." '  XlInsertShiftDirection
 dicLookupTableMSRD.Add Key:=0, Item:="xlFormatFromLeftOrAbove or 0: Newly-inserted cells take formatting from cells above or to the left." '  Default .. xlInsertFormatOrigin Enumeration   https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method
 dicLookupTableMSRD.Add Key:=1, Item:="xlFormatFromRightOrBelow or 1: Newly-inserted cells take formatting from cells below or to the right." '  xlInsertFormatOrigin Enumeration
 ' Range to be copied to Clipboard. CHANGE TO EXPERIMENT
 Set rngCopy = Range("D4:E5")
' Set rngCopy = Rows("4:5")
 Let rngCopy.Interior.Color = vbRed
' Function call to return Demo Array to paste out into a Worksheet to demonstrate the Range Property Item arguments for both the two and one argument case, with the two argument case demonstrating the option of using a column Letter for the second argument in that two argument option
 Let RngObj1Area.Value = RangeItemsArgumantsSHimpfGlified(RngObj1Area)
 Columns("B:J").AutoFit
End Sub
'  Simplified one Liner Function
Public Function RangeItemsArgumantsSHimpfGlified(RngOrg As Range) As Variant
 Let RangeItemsArgumantsSHimpfGlified = Evaluate("=" & """RngItm(""" & "&" & "(Row(" & RngOrg.Address & ")" & "-" & "Row(" & RngOrg.Item(1).Address & ")" & ")+1&" & """, """"""" & "&" & "MID(ADDRESS(1,COLUMN(" & RngOrg.Address & ")-COLUMN(" & RngOrg.Item(1).Address & ")+1),2,(FIND(""$"",ADDRESS(1,COLUMN(" & RngOrg.Address & ")-COLUMN(" & RngOrg.Item(1).Address & ")+1),2)-2))" & "&" & """"""") &(""" & "&" & "(Column(" & RngOrg.Address & ")-Column(" & RngOrg.Item(1).Address & "))+1+" & "(((Row(" & RngOrg.Address & ")" & "-" & "Row(" & RngOrg.Item(1).Address & ")" & ")+1-1)*" & RngOrg.Columns.Count & ")" & "&" & """)""")
End Function