How to insert a row of cells using VBA code

Question

My code is as follows. I have Rng set as a range in my looping macro, and this is a sub of that. If a certain condition is met, then the following sub is called:

Sub FKCutandPaste(Rng As Range)

Rng.Resize(, 9).Cut
Rng.Offset(, 19).Insert Shift:=xlDown
Rng.Resize(, 9).Delete Shift:=xlUp

I want this to cut out a section of the data, then paste it away from the active columns. However, the macro seems to just cut out the the required cells, but doesn't move onto the second line.

Am I missing something very small here?

Joe · Accepted Answer · 2013-02-19 17:22:17Z

2

I can't tell you if you have any specific problem. This code works fine for me, with an arbitrary sheet with data (and as its own sub), in Excel 2010:

Sub test()
Dim rng As Range
Set rng = Cells(6, 2)
rng.Resize(, 9).Cut
rng.Offset(, 19).Select
Selection.Insert Shift:=xlDown
rng.Resize(, 9).Delete Shift:=xlUp
End Sub

I added the 'select' bit because that's often helpful in testing - put a break on the select, run to it, then F8 the line; then see what's selected. If it errors right there, then there's something wrong with rng.offset(,19). If it succeeds and selects (something), then you can see where it thinks it should be inserting the data.

Edit: Commenting out the rng.Resize(,9).Delete seems to fix the problem - it's deleting the newly inserted cells. When you use rng.cut, and then paste the cells in, that seems to change the current location of rng. See this:

Sub test()
Dim rng As Range
Set rng = Cells(6, 2)
MsgBox (rng.Address)
rng.Resize(, 9).Cut
MsgBox (rng.Address)
rng.Offset(, 19).Insert Shift:=xlDown
MsgBox (rng.Address)
rng.Resize(, 9).Delete Shift:=xlUp
End Sub

Change 'cut' to 'copy' and the behavior changes. You may want to simply do that - change it to copy - as you delete the original cells anyway.

edited Feb 19, 2013 at 17:22

answered Feb 19, 2013 at 16:14

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1996971 Over a year ago

Hi, Thanks for the reply. Adding the Select part in seems to work, but then the rest of my looping macro starts being applied to the new cell. I can add another line to go back and select the starting cell again, and this would solve my problem, but is there a way to insert the cells 19 columns across while still keeping the initial cell selected?

Joe Over a year ago

Ahh, okay. The real problem is your delete line - it is deleting the line you just inserted! Hit the break point on the rng.resize.delete and you'll see what is happening.

user1996971 Over a year ago

Are you sure that's the problem? In the above code, the selected cell isn't moving, and after those 9 cells are cut out and inserted away, I'm just deleting the empty cells. With your code, I would be deleting the same line I've just inserted, but I don't want to change the selected cell. Is it possible to insert cells offset from a selected cell?? I can't seem to do that without changing what is selected...

Joe Over a year ago

Yep. I just posted a slightly more detailed explanation. Use 'copy' instead of 'cut' and it should work as expected (with ultimately the expected results).

Collectives™ on Stack Overflow

How to insert a row of cells using VBA code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related