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