Inserting and Copying Rows

Hi Willy...

Thank you for your help.. It's great that there is more than one way to get to the solution!

Hi Alan, thanks so much for your help with this.

Your conversation has helped me with other problems I have had. Thankfully now resolved!

@Paul Richardson
Your problem is very easy to solve. Simply copy the entire row and clear the contents of the cells in columns G and H in the new row so they are ready to receiven new data. This macro will do the job:

Sub InsertCopyRow()
With Selection
.EntireRow.Copy
.EntireRow.Insert
Range(Cells(.Row, "G"), Cells(.Row, "H")).ClearContents
End With
End Sub

Hello Paul,
I tend to think of the Excel VBA Range.Insert method as primarily something that
…… makes a space to put new range in...............
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: 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 kames 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 point 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
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.

I will contact you at the forum further as there is more space for exchanging codes and files etc.

Alan

Hi Alan, thanks for your reply.

I run the code and can see what you mean.

My sheet is a basic project manager. (Sheet Name "ToDoList")

The sheet consists of Cols A : N

I would want to be able to copy any row (Active Row) from the sheet to paste and duplicate it by either:

1, inserting a row below the selected row.
2, Pasting it in the next available blank row in the same worksheet.

Number 1 would be the preferred solution.

The Cols I don't want copied, so left blank are G & H

The reason for this is that Col G is project status and H would be Completion date. And if I am creating a new project, I want these in an blank status.

Hope this makes sense.

Also the code you supplied has helped me with another small problem, so thanks again.. it is appreciated!

PS... I have left this thread on here, but I have also registered on your small forum.

Hello Paul Richardson
VBA does allow you to copy parts of a row. This is because VBA in general will allow you to copy non contiguous cells, as long as they are “in line”.
So for example, any of these would work, ( The first one is copying some columns in a single row)

Range("A2,C2,H2").Copy
Range("A2:A4,C2:C4,H2:H4").Copy
Range("A1:A2,A5:A7,A19").Copy
Range("A1:B2,A12:B20").Copy

This however would not work:
Range("A2,C4,H2").Copy

The problem is that when you paste out any of the successful copied cells , VBA removes the spaces between.

To see what I mean put some random values in cells A2 C2 and H2 of the active worksheet, then run this
Sub Testie()
ActiveSheet.Range("A2,C2,H2").Copy
ActiveSheet.Paste Destination:=ActiveSheet.Range("A4")
Application.CutCopyMode = False ' Empty Excel Clipboard
End Sub


It would not be difficult to do what you want to do, but you would need a slightly more complicated code than that which Allen Wyatt has presented. It would need to loop through and copy and paste each column cell that you want copied. As often, there are almost infinite ways to do something like that in VBA. If you need more specific help then you would need to give a bit more specific details to which cells to be copied, where they are to be pasted etc…

( One of the larger Excel forums are usually the best place to ask such questions. But I don’t mind you asking me here or over at “my“ small forum, ExcelFox. )

Alan Elston

Hi... Very new to VBA but getting there slowly. I have used your code above with great results, but I have now come across a problem. I want to copy the active row, but only copy a selection of the columns, not all of them. Sorry if this isn't the correct place to request this. Many thanks. TallPaulUK