Results 1 to 10 of 10

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

  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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

    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
    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
    Last edited by DocAElstein; 02-19-2018 at 04:16 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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.
    : 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.
    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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

    _.________________________________________________ _________________
    Last edited by DocAElstein; 01-24-2018 at 08:45 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    __________________________________________________ ______________

    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #5
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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!"
    '
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #9
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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



    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


    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 1
    Last Post: 06-26-2014, 12:50 PM
  2. Replies: 2
    Last Post: 02-27-2014, 05:01 PM
  3. Adapt VBA Code With Adjusment Range
    By muhammad susanto in forum Excel Help
    Replies: 2
    Last Post: 09-14-2013, 11:50 AM
  4. VBA Looping Input Range and Output Range
    By Whitley in forum Excel Help
    Replies: 7
    Last Post: 04-25-2013, 09:02 PM
  5. Replies: 7
    Last Post: 04-21-2013, 07:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •