How to insert rows in Excel using macro

posted in: Excel Tutorials | 4



Microsoft Excel Tutorial – How to insert a row in excel using VBA

excel smalll

How to insert rows in Excel using Macro – Excel VBA Insert Row

For a guide to how to edit macros for your own purposes, please click here.

In the video below we outline and explain how we can create a Macro in Microsoft Excel which will add a new row to our spreadsheet. This is very useful if you would like new data to appear at the top of your spreadsheet but keep data in chronological order. This macro uses VBA code which we will term “Insert Row VBA” and can be found in more detail below. This Excel Macro will Insert a row whenever the button is clicked. This Excel VBA is also handy when you want to insert multiple rows, as you can quickly and repeatedly press the created macro which will quickly insert new rows in Excel using the macro.

If you have any questions please post these onto the YouTube channel.

 

Excel Macro Insert row Video

 

The Insert Row Excel VBA code we outline in this tutorial is as follows.

 

Insert Row VBA 

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

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

You will need to change the Sheets(“Sheet1”) and Range(“A4”) to match your own needs. For example, if the sheet you was working on was named “Customers” and you wanted to add a new row to cell A3, these values would display like so.

Sheets(“Customers”).Range(“A3”).Select

As we mentioned above you can press this macro multiple times consecutively and the Excel VBA will insert multiple rows.

To download a copy of the workbook used in this video, please click the link below.

 

Insert New Row Workbook

 

For more YouTube videos please visit my YouTube channel.

If this has been helpful in your projects then please like/subscribe to the channel.

4 Responses

  1. […] Creating a Macro in Excel which will add a new row […]

  2. Hi Thanks for sharing this. However when i have tried to use the same code, i get an excel syntax error highlighting this line of code. any idea how to fix?

  3. Vasant Bhanushali

    we are using excel2007.

    while using this it gives an error ” Compile error ” Syntax error

    and highlights the third line in red from the below

    Sheets(“Sheet1”).Range(“A60”).Select
    ActiveCell.EntireRow.Insert Shift:=xlDown

    Sheets(“Sheet1”).Range(“A60:E60”).Select
    Selection.Borders.Weight = xlThin

    If i delete the last two lines , it gives an error “runtime error 9
    subscript out of range

    regards

  4. Thanks. I can see this a being useful.

Leave a Reply