Notes in support of these main Threads and posts:
http://www.excelfox.com/forum/showth...t-new-range-in
https://excel.tips.net/T002042_Inser...ying_Rows.html
*** see refs also
I tend to think of the Excel VBA Range.Insert method*** as primarily something that …… makes a space to put new range in ……( https://tinyurl.com/y2cup4o8 )
If something happens to be in the clipboard when you use this code line, then VBA makes some assumption that you wanted what is in the clipboard put in the space. Exactly what it decides to do takes a book of explanation, ( http://www.excelfox.com/forum/showth...ll=1#post10441 ). It is left for us to figure out as there is no documentation that I know of
In the simplest case of having a single row in the clipboard, and using the Range.Insert to make a whole row as a space, then the results are as you expect, since the obvious choice that Excel makes is to assume you want to put that copied row in the new row space.
That is what Allen Wyatt's*** first routine does. It inserts a new empty row at the active row ( or row containing the active cell ) , but also what is in the clipboard, ( the original row ) , is put into this new row. What actually is happening is that the original row has been shifted down. A copy of that original ( which was firstly made ) , is put in the new row space
In the second routine, he uses the Range.Insert method with nothing in the clipboard so that it just makes the row space, ( this time he inserts that space one row down ). Then he copies the row and pastes it into the new row space.
So his two routines demonstrate well the points I am trying to make.
To copy just some of a row to a new row:
Using something similar to the second routine is the better alternative for only copying some of the original row to the new row space. ( The other alternative of trying to manipulate and then predict how the Range.Insert will handle a modified clipboard content is certainly possible and academically interesting, but might be a little advanced if you are VBA beginner, especially as the various Bugs and unknowns in the various Microsoft clipboards has been proven in recent years to be beyond the understanding of the Microsoft programmers themselves!! )
So, for example, the general idea would be
_1,
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
Or, as example
ActiveCell.Offset(1, 0).EntireRow.Resize(1, 14).Insert Shift:=xlDown
_2, The simplest way, or first idea that springs to mind for me, is to copy the row info you want to a simple array, and then manipulate it to remove/ leave blank the info that you do not want, then paste that into the space.
Here is a couple of routine examples, here I want just columns A to F from a row A to H
Code:
Sub Testies2a()
Rem 1 make a space by moving ( Shift -ing ) the rows down
ActiveCell.Offset(1, 0).EntireRow.Resize(1, 8).Insert Shift:=xlDown ' when I Re size a range, it starts at the top left of the range, which will be in the first column either for a row or for a range starting in column 1
Rem 2 put the current active row in an array
Dim arrAH() As Variant ' The reason I need Variant is that the next line allows me to capture an entire range in one go, using the .Value property which returns the values in Variant type elements. So the type definition must match.
Let arrAH() = ActiveCell.EntireRow.Resize(1, 8).Value ' so my array now holds cell values from columns A-H ( columns 1-8 )
'2b remove the values you do not want
Let arrAH(1, 7) = "": Let arrAH(1, 8) = ""
Rem 3 paste out modified array
Let ActiveCell.Offset(1, 0).EntireRow.Resize(1, 8).Value = arrAH() ' VBA lets me do the opposite of the capture to paste out array values in oone go
End Sub
Sub Testies2b()
Rem 1 make a space by moving ( Shift -ing ) the rows down
ActiveCell.Offset(1, 0).EntireRow.Resize(1, 8).Insert Shift:=xlDown ' when I Re size a range, it starts at the top left of the range, which will be in the first column either for a row or for a range starting in column 1
Rem 2 put the current active row in an array
Dim arrAH() As Variant ' The reason I need Variant is that the next line allows me to capture an entire range in one go, using the .Value property which returns the values in Variant type elements. So the type definition must match.
Let arrAH() = ActiveCell.EntireRow.Resize(1, 6).Value ' so my array now holds cell values from columns A-F ( columns 1-6 )
Rem 3 paste out modified array
Let ActiveCell.Offset(1, 0).EntireRow.Resize(1, 6).Value = arrAH() ' VBA lets me do the opposite of the capture to paste out array values in oone go
End Sub
As example, if you start with this, with say cell B2 selected:
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
A1 |
B1 |
C1 |
D1 |
E1 |
F1 |
G1 |
H1 |
I1 |
2 |
A2 |
B2 |
C2 |
D2 |
E2 |
F2 |
G2 |
H2 |
I2 |
3 |
A3 |
B3 |
C3 |
D3 |
E3 |
F3 |
G3 |
H3 |
I3 |
4 |
A4 |
B4 |
C4 |
D4 |
E4 |
F4 |
G4 |
H4 |
I4 |
Then using that test data, after running either routine you will get this:
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
A1 |
B1 |
C1 |
D1 |
E1 |
F1 |
G1 |
H1 |
I1 |
2 |
A2 |
B2 |
C2 |
D2 |
E2 |
F2 |
G2 |
H2 |
I2 |
3 |
A2 |
B2 |
C2 |
D2 |
E2 |
F2 |
|
|
I3 |
4 |
A3 |
B3 |
C3 |
D3 |
E3 |
F3 |
G3 |
H3 |
I4 |
_._________________________________
The next routine, I think will do something close to your specific question, the row to be copied is columns A-N , and you want G and H left blank
Code:
Sub Testies3()
Rem 1 make a space by moving ( Shift -ing ) the rows down
ActiveCell.Offset(1, 0).EntireRow.Resize(1, 14).Insert Shift:=xlDown '
Rem 2 put the current active row in an array
Dim arrAH() As Variant '
Let arrAH() = ActiveCell.EntireRow.Resize(1, 14).Value ' so my array now holds cell values from columns A-N ( columns 1-14 )
'2b remove the values you do not want
Let arrAH(1, 7) = "": Let arrAH(1, 8) = ""
Rem 3 paste out modified array
Let ActiveCell.Offset(1, 0).EntireRow.Resize(1, 14).Value = arrAH() '
End Sub
ref:
Allen Wyatt *** https://excel.tips.net/T002042_Inser...ying_Rows.html
*** https://docs.microsoft.com/en-us/off...l.range.insert
Reply With Quote
Originally Posted by rider@1234
Bookmarks