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

Printable View

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

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
:rolleyes:

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.
:rolleyes:
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
A

B

C

D

E

F

G

H

I

J

K

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

Worksheet: RangeInsert

Codes
Sub MeOwl() here:
' https://www.excelforum.com/developme...ml#post4822823
Sub SpreadApartSlipInGetColoured() here:
Full Code: https://pastebin.com/nVaPWF5U
First half : https://www.excelforum.com/developme...ml#post4827914
Second half : https://www.excelforum.com/developme...ml#post4827918

01-24-2018, 08:40 PM
DocAElstein

Using the VBA Range.Insert Code line with nothing in the clipboard. Demo Codes
Spreading Apart For Slipping It In
Strange things can happen if there is something in the clipboard, discussed later. Here is a summary of just using the VBA Range.Insert Code line to squeeze in a new range into a spreadsheet. Make sure your Clipboard is empty !!

Copy all the codes given to a normal code module. To keep things simple the code works on the active worksheet ( the one you would be "looked at" when you run the codes.)
Run code _ Sub _ MeOwl initially to get a simple test range. Yellow is intended to represent some arbitrary working range that you are interested in. ( The red range will be used later for the case of when something is in the clipboard ) Assume you are not too interested in anything a long way outside this range, so empty cells can "slip off" the edge of the worksheet when we shift cells to make a space, and we are not bothered about that. ( Note again: if you had things around the right and bottom perimeters then the VBA Range.Insert Code line might not work in order to prevent anything you have slipping of the edge in a shift action )

The main demo code for the empty clipboard case is then _ Sub SpreadApartSlipInGetColoured _ , so run that code.
The point of the code is to step you through what I believe is going on with the Range.Insert , and at the same time I try to do it in what I believe is a more logical description of the process, in other words , assuming the code line looked more like a three parameter argument code line like
_ Shift _ Direction:=__, TrialRange:=__, FormatOriginForNewCells:=__
The main code sections are:
Rem 1 Direction:=
Rem 2 TrialRange:=
Rem 3 FormatOriginForNewCells:=
The code takes in the required info in that above argument parameter order, 1 , 2, 3, which I think helps make more clear what the _ "VBA Range.Insert Method: Code line makes a space to put new range in" _ is all about

Rem 1' "Shift Method" "Direction:="
This section just takes in that wanted direction. It asks if you want to shift down. ( This is the default in Range.Insert. ( Shift:=xlShiftDown ) ) If you say _ No _, then that is taken as meaning that it is wanted to shift to the right, ( Shift:=xlShiftToRight )
So in effect the user can chose To the Right or Down. These are the only two options available. This makes sense to me as Excel generally likes to do things from a Top Left Origin. - Whilst it is seems happy to select a cell "back up" or "back to the left", in doing any sort of updating/ changing, as we are here, it seems to like going right across doping stuff, then down back "LineFeed carriage return" to the original point but a bit down, then right across doping stuff, then…….. etc.

Rem 2' "Shift Method" "TrialRange:="
I do some seemingly unnecessarily steps remaking a range object that I already have.
:rolleyes: : I am intending to demonstrate a more logical approach to the thing in general. My thinking is that for the sake of convenience a more correct syntax logic was not done by Microsoft. Or the particular software engineer wrote the Range.Insert was drunk or is an idiot that does not understand OOP stuff
What I do in the code is that a specific range object is inputted via the VBA Application.InputBox(…. Type:=rangeObjectreturn( 8 )), which I then breakdown into its full "string reference" for the range object which I then use in the Application.Range(" ") to return the same range object. :rolleyes:
I do this as I am only using the Application.InputBox(…. Type:=rangeObjectreturn( 8 )) as a convenient way to get the user to indicate the spreadsheet area that should be attempted to be made available / opened up or place in the spreadsheet to try to spread apart to make space for an attempt at a new range Insert. My thinking is that a more professional and Hierarchical Object Orientated Programming syntaxly correct approach would have been to take in this string as a string argument to a Shift _ Method or Shift _ Function
In the places that I use the actual Range.Insert in my code I tend to consider it as a "black box"
( Note: In fact the use of the "string reference" proved useful throughout the code as a means of getting at the new range, - the range object produced from this initially in this section, rngNewAttemptAndShift , cannot be used later as the rngNewAttemptAndShift itself is shifted by applying .Insert to it . This is another reason why I find this Range.Insert somewhat questionable in its integrity. Clearly it is a Range.Shift or Ranges.Shiftes )
So the result of Microsoft's muddle is that I have to be very careful what range I am referring to, and will frequently use the_..
_ Application.Range("=FullStringrreferrence")
( _ Application.Range(refNewRngAreaAttempt) _ )
_..as a safe and bullet proof way to refer to the range attempted to be inserted. The place holding place holder variable used initially, rngNewAttemptAndShift , becomes a different range object via rngNewAttemptAndShift.Insert
This last point is very important to realise, and an extra Info. Message box informs of the new worksheet address currently held by the range object used for the Range.Insert. I know this is already totally confusing. That is one of the main points that I am trying to make: This Range.Insert just has not been well thought out in my opinion
01-24-2018, 08:40 PM
DocAElstein

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

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

_.________________________________________________ _________________
01-24-2018, 08:46 PM
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
01-24-2018, 08:48 PM
DocAElstein

Code for last few posts

First Code for last few posts

Code:

' 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
01-24-2018, 08:49 PM
DocAElstein

First half of Code .. Sub SpreadApartSlipInGetColoured()

Code:

' https://pastebin.com/nVaPWF5U ' ' https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822823 Sub SpreadApartSlipInGetColoured() ' https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method 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 Rem -1 Call MeOwl: Application.Wait (Now + TimeValue("0:00:02")) ' Run code Sub MeOwl initially to get a simple test range. Yellow is intended to represent some arbitrary working range that you are interested in. ( The red range will be used later for the case of when something is in the clipboard ) Assume you are not too interested in anything a long way outside this range, so empty cells can "slip off" the edge of the worksheet when we shift cells to make a space, and we are not bothered about that. ( Note again: if you had things around the right and bottom perimeters then the VBA Range.Insert Code line might not work in order to prevent anything you have slipping of the edge in a shift action ) Let Application.CutCopyMode = False ' Strange things can happen if there is something in the clipboard: ' -1(ii) get some convenient strings for later use Rem 0' Adjust slightly the demo range for this simple nothing in clipboard case. Let rngCopy.Interior.Color = vbYellow: Let Application.CutCopyMode = False ' In this code we are not interseted in investigating effects of doing a Copy before using the "...Range.Insert Method Code line makes a space to put new range in...." Rem 1' "Shift Method" "Property Direction" "Direction:=" Dim Q_ShftDown As Long: Let Q_ShftDown = MsgBox(prompt:="Shift Down ?( Answer Yes to shift down or No to spread cells to the right)", Buttons:=vbYesNo, Title:="Shift/ Spread/ Move Spreadsheet cells to Add new range") ' vbYes 6 vbNo 7 Dim InsertShiftDirectionEnum As Long: Let InsertShiftDirectionEnum = -4161 ' xlShiftToRight -4161 Zellen nach rechts verschieben If Q_ShftDown = 6 Then Let InsertShiftDirectionEnum = -4121 ' xlShiftDown -4121 Cells shift down 6 is vbYes Rem 2' "Shift Method" "Initial attempted size in spreadsheet to expose for new range" "TrialRange:=" Property Area for attempted insert Dim rngNewAttemptAndShift As Range ' Input box with option for range input is I use simply as it is convenient for a spreadsheet Range selection Set rngNewAttemptAndShift = Application.InputBox(prompt:="Select a range for insert attempt, then hit Enter or ""OK""", Title:="Posistion and size of space to make for new range. Insert Area attempt", Default:=Selection.Address, Type:=8) Dim refNewRngAreaAttempt As String ' I am deliberately going a bit back and forth here to try to demonstrate a more logical approach to the thing in general. My thinking is that for the sake of convenience a more correct syntax logic was not done. In the places that I use the actual Range.Insert in my code I tend to consider it as a "black box" code line. Let refNewRngAreaAttempt = "=" & "'" & rngNewAttemptAndShift.Parent.Parent.Path & "\" & "[" & rngNewAttemptAndShift.Parent.Parent.Name & "]" & rngNewAttemptAndShift.Parent.Name & "'" & "!" & rngNewAttemptAndShift.Address & "": Debug.Print refNewRngAreaAttempt ' Ctrl+g to get debug Window to check this tricky format Set rngNewAttemptAndShift = Application.Range("" & refNewRngAreaAttempt & "") rngNewAttemptAndShift.Insert Shift:=InsertShiftDirectionEnum: Application.Range("" & refNewRngAreaAttempt & "").Clear ' This black box code line I use to achieve the point in the process just before the last argument condition is added. ' Note I cannot so this : rngNewAttemptAndShift.Clear because rngNewAttemptAndShift is shifted also .. but this is convenient for the over next line '_- '_- '2b) '4a)(i).... Just for Info... Dim rngNewAttemptedAndShifted As Range: Set rngNewAttemptedAndShifted = rngNewAttemptAndShift rngNewAttemptedAndShifted.Select MsgBox prompt:="Note:....Just for Info... the range object that you selected..." & vbCrLf & "Now has Address " & rngNewAttemptedAndShifted.Address, Title:="Note: The address of your selected range also changed due to the shift!" '
01-24-2018, 08:50 PM
DocAElstein

Second half of Code .. Sub SpreadApartSlipInGetColoured()

Code:

' https://pastebin.com/nVaPWF5U Rem 3' FormatOriginForNewCells:= , optional parameter argumant for "Shift Method" Format origin Copy origin for Formats ( where does the Format come from ) ' https://pastebin.com/nVaPWF5U '3a) Determine users preference Dim Q_FrmatFrmUpOrleft As Long ' take this in from two similar Msgboxes If Q_ShftDown = vbYes Then ' We are shifting down, so next choice is format from above or below Let Q_FrmatFrmUpOrleft = MsgBox(prompt:="New range Format from above? ( Answer Yes for above or No for from below )", Buttons:=vbYesNo, Title:="use foramt from above/left or below/right") ' vbYes 6 vbNo 7 Else ' We are shifting right , so next choice is format from right or left Let Q_FrmatFrmUpOrleft = MsgBox(prompt:="New range Format from left ? ( Answer Yes left or No for right)", Buttons:=vbYesNo, Title:="use foramt from above/left or below/right") ' vbYes 6 vbNo 7 End If Dim FormatCopyOrigin As Long: Let FormatCopyOrigin = 0: ' Default: xlFormatFromLeftOrAbove or 0: Newly-inserted cells take the formatting from cells above or to the left. If Q_FrmatFrmUpOrleft = 7 Then Let FormatCopyOrigin = 1 ' 7 is vbNo xlFormatFromRightOrBelow or 1: Newly-inserted cells take formatting from cells below or to the right '3b) Determine Full Copy range for Formats If InsertShiftDirectionEnum = -4121 Then ' xlShiftDown -4121 Cells were shifted down Dim rngCopyOriginFullRwoffset As Long, rngCopyOriginFullClmoffset As Long: Let rngCopyOriginFullRwoffset = 0: Let rngCopyOriginFullClmoffset = 0 ' To be used to determine navigation vectors to Top Left of Range to Copy to get Formats If FormatCopyOrigin = 0 Then ' user has shifted down and wants to take format from above, we need to determine rngCopyOriginFullRwoffset which will be negative Let rngCopyOriginFullRwoffset = -1 * rngNewAttemptAndShift.Rows.Count ' This will take us back up to a Top left one rngNewAttemptAndShift Area back up Else ' user has shifted down and wants to take format from below Let rngCopyOriginFullRwoffset = rngNewAttemptAndShift.Rows.Count ' this will take the Top left one rngNewAttemptAndShift down End If Else ' InsertShiftDirectionEnum = -4161 ' cells were Shift To the Right If FormatCopyOrigin = 0 Then ' user has shifted right and wants to take format from left, we need to determine rngCopyOriginFullClmoffset which wil be negative Let rngCopyOriginFullClmoffset = -1 * rngNewAttemptAndShift.Columns.Count ' This will take us back left to a Top left one rngNewAttemptAndShift Area to the left Else ' user has shifted right and wants to take format from across to the right Let rngCopyOriginFullClmoffset = rngNewAttemptAndShift.Columns.Count ' this will take the Top left one rngNewAttemptAndShift across to the right End If End If ' End determining which direction cells were shifted to make space for new cells Dim rngCopyOriginFull As Range ' from where range should be copied to get formats for new range Set rngCopyOriginFull = Application.Range("" & refNewRngAreaAttempt & "").Offset(rngCopyOriginFullRwoffset, rngCopyOriginFullClmoffset) ' This should be the complete range from which to copy Formats ' Copy range rngCopyOrigin Then change it to get just the single width nearest range, then paste in a special way across the full New range , that is to say only formats rngCopyOriginFull.Copy ' Range.Copy method fills the clipboard with many links to the range I expect so that all aboout it can be got Application.Wait (Now + TimeValue("0:00:03")) ' Pause to show full selected range '3c) Determine first perimeter single width.... For the case of a multi row new Insert Area for a down shift , only the format of the first row is used. For a multi column Insert Area for a right shift, only the format of the first column is used Dim rngCopyOrigin As Range ' This will eventually be a reduced size of the rngCopyOriginFull _For : ... _For the case of a multi row new Insert Area for a down shift , only the format of the first row is used; ... _For a multi column Insert Area for a right shift, as only the format of the first column is used Set rngCopyOrigin = rngCopyOriginFull If InsertShiftDirectionEnum = -4121 Then ' xlShiftDown -4121 Cells were shifted down If FormatCopyOrigin = 0 Then ' user has shifted down and wants to take format from above, we need to resize the rngCopyOrigin to a single row and offset it by the rows count -1 to bring it to the last row in the Copy range Set rngCopyOrigin = rngCopyOrigin.Offset(rngCopyOrigin.Rows.Count - 1, 0).Resize(1) ' Offset first, we lose the row count for the resize, No place holder comma , is required when the final dimension is not resized Else ' user has shifted down and wants to take format from below, we only need to resize to 1 row Set rngCopyOrigin = rngCopyOrigin.Resize(1) End If Else ' InsertShiftDirectionEnum = -4161 ' cells were Shift To the Right If FormatCopyOrigin = 0 Then ' user has shifted right and wants to take format from left, we need to Offset by the columns count -1, then resize to 1 column Set rngCopyOrigin = rngCopyOrigin.Offset(0, rngCopyOrigin.Columns.Count - 1).Resize(, 1) Else ' user has shifted right and wants to take format from across to the right so we only need to resize copy range to 1 column Set rngCopyOrigin = rngCopyOrigin.Resize(, 1) End If End If ' End determining which direction cells were shifted to make space for new cells rngCopyOrigin.Copy ' copy the 1 perimeter width range Application.Wait (Now + TimeValue("0:00:03")) ' Pause to show the selected the 1 perimeter width range Application.Range("" & refNewRngAreaAttempt & "").PasteSpecial Paste:=xlPasteFormats Rem 4 Final "black box" code line using all parameters MsgBox prompt:="The previously done will all be ""deleted"", then the same will be done using the one line VBA Range.Insert" '4a)(Determine direction to shift back, then use Range.Delete method to return to the original situation Application.Wait (Now + TimeValue("0:00:01")) Dim DeleteShiftDirectionEnum As Long ' get the corrsponding "reverse" direction to the used InsertShift direction Select Case InsertShiftDirectionEnum Case -4121: Let DeleteShiftDirectionEnum = -4162 ' xlShiftDown -4121 -- xlShiftUp -4162 Zellen werden nach oben verschoben. XlDeleteShiftDirection Enumeration xlShiftUp -4162 Cells are shifted up. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-delete-method-excel Case -4161: Let DeleteShiftDirectionEnum = -4159 ' xlShiftToRight -4161 -- xlShiftToLeft -4159 Zellen werden nach links verschoben. XlDeleteShiftDirection Enumeration xlShiftToLeft -4159 Cells are shifted to the left End Select 'Dim arrVls() As Variant: Let arrVls() = rngNewAttemptAndShift.Value ' the .Value Property returns a Field of variant types with the values of the range to which it is applied. Note thes values may be Empty, Values, Formulas Application.Range("" & refNewRngAreaAttempt & "").Delete Shift:=DeleteShiftDirectionEnum ' This I find good Hierarchical Object Orientated Programming syntaxly correct approach 'Let Application.Range("" & refNewRngAreaAttempt & "").Value = arrVls() ' We may assign the values of an Array directly to a spreadsheet range MsgBox prompt:="Finally, the standard code line will be used, based on your given options" Rem 4 Final "black box" code line using all parameters ... Application.Wait (Now + TimeValue("0:00:01")) ' Short pause, then all the above will be repated with the standard Range.Insert code line Application.Range("" & refNewRngAreaAttempt & "").Insert Shift:=InsertShiftDirectionEnum, CopyOrigin:=FormatCopyOrigin ' https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method MsgBox prompt:="You would use the standard code line Range.Insert Shift:=__ ,CopyOrigin:=__ " & vbCrLf & "as follows(all as one line): " & vbCrLf & "Range(""" & refNewRngAreaAttempt & """)" & ".Insert Shift:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(InsertShiftDirectionEnum), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(InsertShiftDirectionEnum), " ", vbTextCompare)) & ", CopyOrigin:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(FormatCopyOrigin), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(FormatCopyOrigin), " ", vbTextCompare)) & "", Title:="Application.Range Full version Range.Insert code line" MsgBox prompt:="Simplified for Active Worksheet," & vbCrLf & "Copy following(all to one line): " & vbCrLf & "Range(""" & Replace(VBA.Strings.Mid$(refNewRngAreaAttempt, (VBA.Strings.InStr(1, refNewRngAreaAttempt, "!", vbTextCompare) + 1)), "$", "", 1, -1, vbTextCompare) & """).Insert Shift:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(InsertShiftDirectionEnum), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(InsertShiftDirectionEnum), " ", vbTextCompare)) & ", CopyOrigin:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(FormatCopyOrigin), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(FormatCopyOrigin), " ", vbTextCompare)) & "", Title:="The below, (all on one line), is the final standard code line" Debug.Print "Range(""" & Replace(VBA.Strings.Mid$(refNewRngAreaAttempt, (VBA.Strings.InStr(1, refNewRngAreaAttempt, "!", vbTextCompare) + 1)), "$", "", 1, -1, vbTextCompare) & """).Insert Shift:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(InsertShiftDirectionEnum), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(InsertShiftDirectionEnum), " ", vbTextCompare) - 1) & ", CopyOrigin:=" & VBA.Strings.Left$(dicLookupTableMSRD.Item(FormatCopyOrigin), VBA.Strings.InStr(1, dicLookupTableMSRD.Item(FormatCopyOrigin), " ", vbTextCompare)) & "" End Sub
01-24-2018, 08:51 PM
DocAElstein

Using the VBA Range.Insert Code line when something is in the clipboard

If anything other than a spreadsheet range is in the clipboard range then the behaviour of the VBA Range.Insert Code line is as discussed previously.
What happens when a range is copied to the clipboard followed by a Range.Insert Code line is not simple to explain.
I have not seen any clear official documentation on this. I expect it "works" as a method to Insert range data more by chance than by design.
I can see a set of rules. I can summarise them in 2 Pints

_Pint 1) The attempted new range dimensions.
Unlike in the case for an empty clipboard, it does not follow that the actual range inserted will have the dimensions of that given in by the Range in Range.Insert. This probably goes unnoticed as often this "Method", that is to say this "combination of a Range.Copy followed by Range.Insert" is used to insert whole rows

Consider a " "combination of a Range.Copy followed by Range.Insert" action."
As in the case of nothing in the clipboard, a new Range is given, that being the Range in the Range.Insert Command. At the outset of the Insert command, it would appear that the range to be made free by the Insert command is not necessarily the Range. It appears to me that the Range will be adjusted , according to the Rules below. ( The command may or may not then be successful, that is to say it may or may not error ).

For the purposes of clarity.
Range is that copied to the clipboard
and
Range is the given range in Range.Insert
and
Range is the attempted range used finally in Range.Insert , .. in other words, if the action is successful then the actual action done is Range.Insert
As will be discussed, Range will start at top left of Range but the actual range area used , Range, will be a contiguous rectangular range of cells comprising Full multiples of Range

Rules:
_ Pint 1a) Range >= Range
_ P1a) The Range given , ( in the Range.Insert code line ) is equal to or larger than the Copy Range
It appears that the attempted Range is either the copied Range or an exact multiple thereof.
___ P1a)(i) If it is possible to include an exact number of the copied Range dimensions within the Range specified as the new range to be inserted, then that will become the attempted effective Range in Range.Insert. In this case the Range in the Range.Insert command is an integer number of the copied Range held in the clipboard. It will be attempted to insert this Range. Top left of the given insert Range remains Top left of the inserted new Range
___ P1a)(ii) If it is not possible to include an exact number of the copied Range directions within the Range specified as the new range to be inserted, then the range attempted to be inserted will be the copied Range held in the clipboard. . Effectively the Range "shrinks" from that given in darkRange down on size to the copied Range.
Range becomes Range
Top left of the given insert Range remains Top left of the inserted new Range

_Pint 1b) Range is < Range in 1 or more directions
_P1b) The specified Range ( in Range.Insert ) is smaller than the Copy Range in either the horizontal direction or the vertical direction ( or both ).
___P1b)(i) If it is possible to include an exact number of the Copy Range width or an exact number the Copy Range height in the Range specified as the new range to be inserted, then that will become the attempted effective Range in Range.Insert. In this case the Range in the Range.Insert command is increase in the other direction to include the integer number of the Copy Range which do fit in one direction.
Top left of the given insert Range remains Top left of the inserted new Range
___P1b)(ii) If it is not possible to include an exact number of the copied Range width or an exact number of the copied Range height within the Range specified as the new range to be inserted, then the Range attempted to be inserted will be the Copy Range held in the clipboard. Effectively the Range adjusts

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

_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

Sub InsertMyItems() -- Range.Insert when range copied to clipboard

Code and demo range for discussions in last post:

The same range is used as in the experiments for Using the VBA Range.Insert Code line with nothing in the clipboard

Using Excel 2007 32 bit

_Row\^Col
A

B

C

D

E

F

G

H

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)

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)

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)

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)

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)

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)

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)

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)

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)

11

12

Worksheet: RangeInsert

Codes:

Main demo code:

Code:

Sub InsertMyItems() ' Take in Insert range from Current user selection Set rngNew = Selection ' The range selected before running the code is taken as Range ' Set up test range Call MeOwl ' Copy selected range rngCopy.Copy ' Initially rngCopy is set at D4:E5 in Sub MeOwl() This is Range Application.Wait (Now + TimeValue("0:00:01")) ' In the following Range.Insert code line, rngNew (Range) will not necessarily be that finally used. It may be changed https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4827953 Range will start at top left of Range but the actual range area used , Range, will be a contiguous rectangular range of cells comprising Full multiples of Range rngNew.Insert Shift:=xlShiftToRight ' xlShiftToRight xlShiftDown End Sub

Other required Called routines
Note: The first two Dim lines should be at the top of a code module ( under Option Explicit is you have that )

Code:

Dim rngCopy As Range, rngNew As Range Dim dicLookupTableMSRD As Object Sub MeOwl() ' https://www.excelforum.com/developme...ml#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-pro...ml#post4431231 http://www.eileenslounge.com/viewtop...=24955#p193413 https://www.excelforum.com/excel-pro...d-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-...eInsert-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-...eInsert-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 from here: https://www.excelforum.com/developme...ml#post4555457 ' 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

01-24-2018, 08:54 PM
DocAElstein

Range.Insert Conclusions

Conclusions for "Copy Insert thingy"

I think I can explain the Observed rules discussed using Interception Theory
http://www.excelfox.com/forum/showth...on-and-VLookUp
https://www.excelforum.com/tips-and-...d-vlookup.html
A good graphical explanation will have to wait until I have access to and am confident in a good graphical presentation software.
In words for now:
The help matrix in the case of 1a) is in the dimension of the given Range due to the full encompassing of the copy Range limited to multiples of the help matrix for interception of that help matrix. Valid coincidentalled matching vectors by the pseudo Array formulaed rows then columns screen updating will limit to those locking on that grid of repeated copy Range. This reduces to the single copy Range without a valid intersect in the two dimensions.

In the case of 1b) the reduction of any one dimension below copy Range will result in the repeated extended copy for that dimension in the other in the help matrix. This will allow the interception in that dimension for any multiple.

The reason why a shift resulting in a split initial copy Range errors for selected Range other than that on the Top left intersect is due to the forced breaking of any intersections in place caused be the shift and resultant split

:rolleyes:

I think I would avoid the "Copy Insert thingy".
It is bad enough that the Range.Insert Method is not really, what it might appear to be, or is attempted to be explained to be.

Conclusions from Range.Insert Method.
Clearly there is a lot more to it , when one investigates it fully. There appears no documentation to many of the characteristics that I have found.
So it is a partially not known and undocumented thing.
So I would not use it.

https://powerspreadsheets.com/excel-vba-insert-row/
https://msdn.microsoft.com/de-de/vba...t-method-excel
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
https://msdn.microsoft.com/de-de/vba...sgbox-function