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

**DocAElstein** · 01-24-2018, 08:37 PM

VBA Range.Insert Method: Code line makes a space to put new range in.
Aka. Spreading Apart For Slipping It In, but be careful what you have in your Clip when you do it
Aka Ranges.Shiftes Spread apart and Insert

Range("xxx").Insert Shift:=xlShiftxxx, CopyOrigin:=xlFormatFromxxxOrxxx

Hi
I was relearning this and made myself some notes and comments and codes that I thought I could share…
Firstly I think this method has been very badly organised and does not really fit in too well in the way that it has been presented and often explained.
_ My final conclusions at the end has put me off using it as it is an undocumented, unknown mess in all but the simplest cases…

It is often seen in this basic form, mostly seen without its optional parameter arguments:
_ without its optional arguments:
__ Range.Insert
_ with its options:
__ Range.Insert _ ShiftDirectionDownOrToRight, UseFormatFromAboveOrbelowForNewRange
I think it is better not to think of this initially in terms of anything to do with inserting a range.
I am of the opinion that it should have been described in its basic form as a

Shift _ Method
Or
Shift _ Function
So something of the form
Shift _ Direction:=__, TrialRange:=__, FormatOriginForNewCells:=__
I would suggest that primarily, a space is made by shifting a , ( typically very large ) , number of cells: Most often we make this space somewhere around the left upper corner of what is a massive worksheet, most of which most of us never see or use. So to shift a space that would mean shifting down or to the right a very large amount of columns or a massive amount of rows. That is what actually goes on. So it is more to do with something outside the Range. I can see that it uses dimensions of the Range to determine the "gap" to make, but as it may take formats from outside that range, I think it is less than a method of that range, but rather a function that takes the Range as one of the arguments.
You may be shifting a lot of stuff. You could think of it as to a large extent as changing the Address Property of a large amount of Range objects which are to the right or below the Range. I have often had problems with memory issues when using this method. I guess a lot of memory is needed to allow for things like the back button option when large areas are moved around/ changed

I think the sight of the_..
Range.Insert
_..suggest the Hierarchical Object Orientated Programming ( OOP ), whereby some Property of the Range Object is being applied to it, such as a Method in terms of a Function within the class working on data of the object instance.

The literature does make the distinction of calling Range.Insert a Method of that Range, and not a Property. That distinction is worth noting: A Method which is in an object, typically uses data in that object. To the limited extent that is true in this case, but even that is questionable, as will be shown later in the section of . "Copy Insert thingy".
If you use a specific range object , (Range like rng, rather than hardcoded like Range like Range (" ") ) then you will see that after, rng.Insert, the rng object has also shifted, along with all the other cells below or to the right of it.
Possibly you might consider it better described as a _ Worksheet.Insert_ or _ Worksheet.RangeInsert
But I would suggest that the emphasis of what is going on should be taken away from the Range and Insert, and placed on the _ Shift.

I think it is easy to understand and master this Range.Insert if you think of it as a Shifting of a large number of cells to make space for a new range, the point being that the main result is that you get a new virgin Range. One could argue that it not truly a virgin as formats are given to it from one "side" or the other. But then again it is arguably strange that a third choice of no formatting is not given for the CopyOrigin:=xlFormatFromxxxOrxxx argument.

( Note that Range("xxx") will not work if you try to shift the cells such that any cells with anything in them would as a result be pushed off the worksheet. )

Because I think that it is badly organised and does not really fit in correctly into the OOP way of looking at things, I tent to just look at it as a code line that does a shift and adds a new range
Or a
"Spreading apart for slipping it in" thingy

The use of Range.Insert will give quite different results and some peculiar results if something is in the Clipboard at the time of execution. So it is probably a good idea to look at the two situation separately, starting with the sinplist of when nothing is in the clipboard

I have done some demo codes , and will be using a range generated by part of the codes. The codes and range are also in this file, "RangeReferrencingBlogs2018.xlsm" at this file sharing link:
https://app.box.com/s/li0c4sglihpmvgoslptwg469dlycmd8p

Using Excel 2007 32 bit

_Row\^Col	B	C	D	E	F	G	H	I	J
1
2	RngItm(1, "A") &(1)	RngItm(1, "B") &(2)	RngItm(1, "C") &(3)	RngItm(1, "D") &(4)	RngItm(1, "E") &(5)	RngItm(1, "F") &(6)	RngItm(1, "G") &(7)	RngItm(1, "H") &(8)	RngItm(1, "I") &(9)
3	RngItm(2, "A") &(10)	RngItm(2, "B") &(11)	RngItm(2, "C") &(12)	RngItm(2, "D") &(13)	RngItm(2, "E") &(14)	RngItm(2, "F") &(15)	RngItm(2, "G") &(16)	RngItm(2, "H") &(17)	RngItm(2, "I") &(18)
4	RngItm(3, "A") &(19)	RngItm(3, "B") &(20)	RngItm(3, "C") &(21)	RngItm(3, "D") &(22)	RngItm(3, "E") &(23)	RngItm(3, "F") &(24)	RngItm(3, "G") &(25)	RngItm(3, "H") &(26)	RngItm(3, "I") &(27)
5	RngItm(4, "A") &(28)	RngItm(4, "B") &(29)	RngItm(4, "C") &(30)	RngItm(4, "D") &(31)	RngItm(4, "E") &(32)	RngItm(4, "F") &(33)	RngItm(4, "G") &(34)	RngItm(4, "H") &(35)	RngItm(4, "I") &(36)
6	RngItm(5, "A") &(37)	RngItm(5, "B") &(38)	RngItm(5, "C") &(39)	RngItm(5, "D") &(40)	RngItm(5, "E") &(41)	RngItm(5, "F") &(42)	RngItm(5, "G") &(43)	RngItm(5, "H") &(44)	RngItm(5, "I") &(45)
7	RngItm(6, "A") &(46)	RngItm(6, "B") &(47)	RngItm(6, "C") &(48)	RngItm(6, "D") &(49)	RngItm(6, "E") &(50)	RngItm(6, "F") &(51)	RngItm(6, "G") &(52)	RngItm(6, "H") &(53)	RngItm(6, "I") &(54)
8	RngItm(7, "A") &(55)	RngItm(7, "B") &(56)	RngItm(7, "C") &(57)	RngItm(7, "D") &(58)	RngItm(7, "E") &(59)	RngItm(7, "F") &(60)	RngItm(7, "G") &(61)	RngItm(7, "H") &(62)	RngItm(7, "I") &(63)
9	RngItm(8, "A") &(64)	RngItm(8, "B") &(65)	RngItm(8, "C") &(66)	RngItm(8, "D") &(67)	RngItm(8, "E") &(68)	RngItm(8, "F") &(69)	RngItm(8, "G") &(70)	RngItm(8, "H") &(71)	RngItm(8, "I") &(72)
10	RngItm(9, "A") &(73)	RngItm(9, "B") &(74)	RngItm(9, "C") &(75)	RngItm(9, "D") &(76)	RngItm(9, "E") &(77)	RngItm(9, "F") &(78)	RngItm(9, "G") &(79)	RngItm(9, "H") &(80)	RngItm(9, "I") &(81)
11