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 www.twoperscent.com

Creating a Macro in Excel which will add a new row

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”

Code:
Sheets(“Sheet1”).Range(“A4”).Select
ActiveCell.EntireRow.insert Shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select
Selection.Borders.Weight = xlThin

Thanks for watching!

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

22 thoughts on “Creating a macro in Microsoft Excel that will insert a new row.

    Rosemarie ramírez

    (April 17, 2016 - 6:31 pm)

    For my spread sheet I need the new row to drop down. How would I do that?

    Dee G

    (April 17, 2016 - 6:31 pm)

    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

    Ahmad Azhar

    (April 17, 2016 - 6:31 pm)

    can u teach me how to insert row at the end of the table?

    jebin george

    (April 17, 2016 - 6:31 pm)

    k thanks ..but what I mean ..I need the data also add into that row ..(like copy paste)

    Slava Inberg

    (April 17, 2016 - 6:31 pm)

    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.

    Twoperscent

    (April 17, 2016 - 6:31 pm)

    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.

    https://www.youtube.com/watch?v=w24patkX1Fs
    https://www.youtube.com/watch?v=ko22NviGex0

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

    jebin george

    (April 17, 2016 - 6:31 pm)

    could you explain how to enter data into new row also (like copy paste)

    arunw700

    (April 17, 2016 - 6:31 pm)

    Can you please let me know if I can have a macro that can enter New Line characters at the end of the line in the same cell?

    Brandon Moser

    (April 17, 2016 - 6:31 pm)

    When I click debug this is the line item it points at and highlights in yellow:
    Sheets("Sheet1").Range("A­1").Select
    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?

    Brandon Moser

    (April 17, 2016 - 6:31 pm)

    I keep getting a run-time error 1004? I'm confused.

    Mandie Crawford

    (April 17, 2016 - 6:31 pm)

    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.

    Eric Mooij

    (April 17, 2016 - 6:31 pm)

    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?

    Faraja Wanderlin

    (April 17, 2016 - 6:31 pm)

    Great! I would like to maintain a formula on my last column when adding the rows. Is there a macro  that can do that?

    Felix Rossi

    (April 17, 2016 - 6:31 pm)

    I'm almost in tears here…I can't find the equivalent of this video, for Mac users :(

    Felix Rossi

    (April 17, 2016 - 6:31 pm)

    i just noticed that on mac this looks different… 🙁 will have to keep googling

    Felix Rossi

    (April 17, 2016 - 6:31 pm)

    thank you so much. exactly what I needed and very clearly explained :-)

    Nahidul Islam

    (April 17, 2016 - 6:31 pm)

    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.

    Kendra Majewski

    (April 17, 2016 - 6:31 pm)

    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?

    Mark Politi

    (April 17, 2016 - 6:31 pm)

    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

    Jessy Josichan

    (April 17, 2016 - 6:31 pm)

    This is the code for the borders for the new row right? Selection.Borders.Weight = x1Thin
     It's not working for me! plz help +Twoperscent !!

    Bob Galik

    (April 17, 2016 - 6:31 pm)

    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.

    Bhaskar Rao

    (April 17, 2016 - 6:31 pm)

    Hi, Thanks a lot for the tutorial.

Leave a Reply

Your email address will not be published. Required fields are marked *