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
Bookmarks