Creating a macro in Microsoft Excel that will insert a new row.


About this tutorial:

Video duration: 5:
You can now download a copy of this worksheet from the link below at

A simple guide on how to create a macro in Microsoft Excel that will insert a gridlined new row at the location you specify. Please enter code below between “Private Sub” and “End Sub”

ActiveCell.EntireRow.insert Shift:=xlDown

Selection.Borders.Weight = xlThin

Thanks for watching!


  1. Hello,

    What if I wanted to record a macro that adds one line below multiple selections?

    Say for example I have a series of data collected into one sheet, and I have everything with the word "total" selected through the find command.

    How would I add a row below the multiple highlighted selections?

    In case you're wondering, right clicking deselects the data, even if the control button is being held simultaneously. And clicking the menu key (second to right, next to the navigation arrows) to insert a row ends up inserting a row above the selected cell.

    hope I didn't confuse you

  2. Thanks so much for the video! What code would I put in to insert a row into a table in a different worksheet? This formula only seems to work if the button is on the same worksheet as the table you are inserting rows in.

  3. Hi All,

    I have created a couple more videos addressing some common questions that I have received with regards to this video, such as how to include formulas in the macros, and also sequential number generation. Please find these videos at the following links below.

    Thanks for the feedback and if you have any further questions please post them on here.

  4. When I click debug this is the line item it points at and highlights in yellow:
    I am running a test so I left the "sheet 1" named sheet 1. I followed your first video on setting up macros. Any suggestions?

  5. I followed the instructions exactly – even recreating the sheet shown in the video. In all cases I get run time error '1004' Application-defined or object-defined error.  I am in excel 2013.

  6. Great tutorial, however I've got an question. The adding of a row works but the only thing is that the new row copies the style of my header row and not the style and formulas of the row in the table itself. 

    Is there a way on doing that?

  7. I want to insert multiple row after a predetermined interval or logic say for example I have a table which contains name for product lines and with price. Usually the price varies with different products. I want to insert row after every price change. Can you help me? I can send you a dummy file of me.

  8. This is very helpful! Is there a way for the range to be a specific cell based on its content? I have multiple headers all in column A, such as "Analog in", "Pressure in", etc. I created a button to add rows below each one, but upon the addition of the row all of the cell numbers change for the lower categories. It is changing the cell number set as the range for the button to insert rows under "Pressure in" and other categories. Is there a way to adjust the code so that  the pressure in button will always insert rows below what ever row" Pressure In' is at?

  9. OUTSTANDING tutorial.  I have a sheet with Column A for numbering new photos.  This Macro is perfect for adding the newest photos at the top.  However, it would be totally excellent if I could have the numbers in column A automatically update  with the addition of each new Row.  For example, if my last photo number was 2043, is it possible to get the top cell in Column A to automatically update to the next highest sequential number when I click "Add Row?"  Thanks so much. –  Mark Politi

  10. The code I'm looking for would simply add a new row to an existing table, keeping the formatting. The table is in a protected worksheet, and excel doesn't allow the creation of new rows while protection is on.


Please enter your comment!
Please enter your name here