Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Inserting and Copying Rows.

Inserting and Copying Rows

by Allen Wyatt
(last updated March 23, 2019)

7

As you are editing worksheets, you may notice that some of your work is done based on work you have done before. For instance, you may have a row of data that you entered in a previous Excel session. In this session, you need to copy that row of data and use it as the basis for your new data, but with a few changes.

In such a situation, it would be nice to have a quick way to enter a blank row after the current row and copy the data in the current row to the new blank row. There are no intrinsic commands in Excel to do this, but a macro can do it very handily. Consider the following example:

Sub InsertCopyRow1()
    ActiveCell.EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

In order to use the macro, all you need to do is select a cell in any row. When the macro is run, a duplicate of the current row is inserted just below the row you are in.

The only problem with this solution is that it leaves the Excel interface a bit "messy" (for lack of a better word). When completed, a complete row is still selected, and the new row has the "marching ants" marquee around it.

This problem can be overcome by including commands to collapse the selection and move it to a desired location. Another way is to simply use a different macro that relies on different VBA commands. The following macro will also insert and copy a row, but it leaves the cell that you selected active:

Sub InsertCopyRow2()
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2042) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Inserting and Copying Rows.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Charts in VBA

Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on ...

Discover More

Two Printed Copies to Different Paper Trays

Many modern printers include multiple paper trays that can be used for different types or colors of paper. Word allows ...

Discover More

Speeding Up Mail Merges

The Mail Merge tool in Word is a great way to create new, customized documents. If you are doing a lot of merging, you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

Relative Worksheet References when Copying

Copy a formula from one cell to another on the same worksheet, and Excel adjusts any relative references in the formula ...

Discover More

Getting Rid of Non-Printing Characters Intelligently

Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...

Discover More

Copying to Very Large Ranges

Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight less than 9?

2019-06-10 07:56:52

Paul Richardson

Hi Willy...

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


2019-06-10 07:54:44

Paul Richardson

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

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


2019-06-09 10:34:25

Willy Vanhaelen

@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


2019-06-08 04:43:40

Alan Elston

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


2019-06-06 05:16:16

Paul Richardson

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.


2019-06-06 02:53:34

Alan Elston

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


2019-06-04 06:25:25

Paul Richardson

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


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.