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!" '
Bookmarks