Inserting Rows in Excel Worksheet using VBA

Home/Excel VBA/Inserting Rows in Excel Worksheet using VBA

VBA insert rows excel macro helps while automating and dealing with the records. For example, we may automate certain task based on the number of items in certain category. And the number of items may not be equal in all the situations it may vary time to time. We will see a practical example in this topic.

How to Insert Rows in Excel Worksheet using VBA – Solution(s):

We can insert use EntireRow.Insert method to insert rows. The following is the example code to inserting rows in excel worksheet.

VBA insert rows excel – An Example

The following example will show you how to insert a row in Excel Worksheet. You can insert multiple rows at a time.

Sub sbInsertingRows()
'Inserting a Row at at Row 2
'Inserting 3 Rows from 3
End Sub
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to run it

Shcreen-shot of example, before executing of the above code. You can see the 10 rows of data available in the worksheet.
Inserting Rows Examples 1


Shcreen-shot of example, after executing of the above code. You can see the 4 new rows are inserted in the worksheet.

Inserting Rows Examples 2

Inserting Rows in Worksheet using Excel VBA – Case study

The following example create the list if items by inserting the rows based on the numbers mentioned for each category.

Sub sbInsertingRowsCaseStudy()
Dim iCntr, jCntr
For iCntr = 2 To 4 ' for each category
'Find the start row of category
startRow = Application.WorksheetFunction.Match(Cells(iCntr, 1), Range("A16:A3300"), 0) + 15 'assuming maximum items are around3000
For jCntr = 1 To Cells(iCntr, 2) 'print items
Rows(startRow + 2).EntireRow.Insert
Cells(startRow + 2, 2) = "Item " & Cells(iCntr, 2) - jCntr + 1
End Sub

Download the example file and click on the ‘Create Category List’, it will create the categories based on the number mentioned for each category.
Inserting Rows Examples and Case study

Inserting Rows in Worksheet using Excel VBA – Download: Example File

You can download the example file and see example codes on Inserting Rows in Excel Worksheet.

ANALYSISTABS – Inserting Rows

By |March 28th, 2013|Excel VBA|27 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.


  1. venkat January 15, 2015 at 4:06 PM - Reply

    I need small information .In excel every 27 rows after insert 5 to do this .Please suggest to me

    • PNRao January 16, 2015 at 2:47 PM - Reply

      Hi Venkat,

      Here the VBA macro to insert n umber of rows after every nth row:

      Sub Insert_Rows_After_Every_Nth_Row()
      lRow = 41 ' last row in your sheet
      'If yoyr data is not fixed:
      'please refere the 100+ useful macro to find last row macro
      everyNthRows = 27 'After every nth row
      NumRowsTobeInserted = 5 'Number of rows to be inserted
      Do While lRow >= everyNthRows
      If lRow Mod everyNthRows = 0 Then Rows(lRow + 1 & ":" & lRow + NumRowsTobeInserted).Insert
      lRow = lRow - 1
      End Sub

      Hope this helps!

      • Krishna March 29, 2016 at 9:04 PM - Reply

        Hi, What if I have a row of heading and want to skip it, the above macro helps when there is no heading but if I have a heading it will go for a toss.

      • Krishna March 30, 2016 at 3:54 PM - Reply

        Hi , What I mean is how to tell the macro to skip first n rows so header wont be affected and then run the macro like above!

  2. Spandan February 16, 2015 at 12:03 PM - Reply

    I want to create a column before some specific text inside the cell .can you please help me in this ?

  3. JH March 11, 2015 at 7:12 AM - Reply

    Hi, I would like to create a number of rows based on a cell input.
    For example, based on input = 3 in a certain cell, I want to create 3-minus-1 rows.
    How do I do this? Many thanks!

    • PNRao March 21, 2015 at 2:35 PM - Reply

      You can write something like this, lets say you are entering the number at Range A1 and you want to insert the new rows starting from Row2:

      Sub sbInsertRowsBasedOnACellValue()
      numberRows = Range("A1")
      InsertRowsAtRow = 2
      Rows(InsertRowsAtRow & ":" & InsertRowsAtRow - 1 + numberRows - 1).EntireRow.Insert
      'Here numberRows - 1 indicates, number mentioned at A1 Minus 1
      End Sub
  4. David March 25, 2015 at 5:23 PM - Reply

    Wondering if this can be modified to suit something I’ve been struggling with:

    The code listed below is tasked to :
    > Create a new sheet based on each page break (which have been inserted through the subtotals function)
    > Save the sheet to a designated location and,
    > Auto-name each sheet according to the value in cell A2.
    Code below:

    “Sub Sample()
    Dim rowCurrent As Long, rowPrevious As Long, i As Long
    Dim oWB As Workbook, newWbk As Workbook
    Dim oWS As Worksheet

    Set oWB = ActiveWorkbook

    Set oWS = oWB.Sheets(“Specials”)

    rowPrevious = oWS.UsedRange.Row + oWS.UsedRange.Rows.Count – 1

    For i = oWS.HPageBreaks.Count To 0 Step -1
    If i = 0 Then
    oWS.Rows(“1:” & rowPrevious).Copy
    rowCurrent = oWS.HPageBreaks(i).Location.Row
    oWS.Rows(rowCurrent & “:” & rowPrevious).Copy
    End If

    ActiveWorkbook.SaveAs “file_path ” & ActiveSheet.Range(“A2″).Value & -i

    rowPrevious = rowCurrent – 1
    End Sub”

    What I want to do, is take the column headings from the main file and insert them as row 1 on each sheet.

    Would greatlly appreciate the assist.


  5. Rob Garven April 7, 2015 at 4:37 AM - Reply

    Good morning

    I am trying to insert 9 blank rows after every line of text and am a loss as to how to write the script. I have had a look at the example above and am wondering what the script should look like?

    Thank you in advance

  6. Erin Stack April 14, 2015 at 6:10 PM - Reply

    I am trying to add an add row button to several sections in Excel. I can execute the commands and get the rows to add but when it runs if you add a row to section 1 at the end of the existing rows it works fine, but in section two the new row is added within the section and not at the end of the section. How do I write the script to always add a row at the end of the section regardless of the new rows added above the section?

    • Simon July 30, 2015 at 7:43 PM - Reply

      Hi Erin

      I’m struggling with this one too. It does work if you click on one of the rows within the range of data before you run the macro though.

      • PNRao July 30, 2015 at 9:09 PM - Reply

        Hi Simon,

        Could you please explain your requirement and share the code which you have tried.


  7. Mike May 27, 2015 at 2:59 AM - Reply

    I have been looking for a way to add rows to a worksheet based on the number in a cell. This is the only place (of many) I found something that works. Thanks!


    • Ash October 12, 2015 at 3:07 PM - Reply

      Can you share the code please. I am unable to use the above one.

      • PNRao October 22, 2015 at 11:28 PM - Reply

        Hi Ash,

        Please click on Download Now to download the example file and see the example codes on Inserting Rows in Excel Worksheet at the end of the page.


  8. jay July 18, 2015 at 1:06 PM - Reply

    must have to set object variable

  9. Nikos November 4, 2015 at 2:54 PM - Reply

    I find very useful all your informations!
    I have a question: I have a workbook and I want to add an intire row that contains data or functions, directly beneath. The “problem” is that there are other rows that I want them to move down. For example, if I want to copy row A1, there are data in row A2,AE etc and I want them to move one row down so I can stell use them..
    Thanks in advance for your reply

  10. Prakash February 11, 2016 at 9:32 PM - Reply

    Hi I wants to add row if data/value found, else not.

    so, there are many line items but not very specific sequence.

    please provide macro.

    Thank you

  11. FT February 24, 2016 at 2:40 AM - Reply

    Hi, How can I copy the formulas in a row to the next row.

  12. usha March 1, 2016 at 2:02 PM - Reply


    I want a macro for rows that are having phone numbers more than 1 e.g.,

    xxxxxx 234555, 455555, 5677777, 567778
    xxxxxxxx 455656
    fdgggfdf 7878787, 455550
    xxxxxxxx 455656

    xxxxxx 234555
    xxxxxx 455555

    e.g., 234555, 455555, 5677777, 567778 these numbers should come one after the other by using macro code.

    These can occur randomly on my data sheet, it can be 2, 3, 4, or 5 numbers. But they will have the same name in separate colm.

    I want them to be inserted as new cells, so that they do not overlap the numbers below

  13. Nayan May 10, 2016 at 12:01 AM - Reply

    Hi I need a macro which will insert multiple rows below consecutive rows and insert particular data in the newly insrted rows.
    Example :-
    33498 000001 ABC
    33498 000001 PQR
    33498 000001 MNB
    33498 000001
    33498 000001 ASD
    33498 000001 AZXC
    33498 000001
    33498 000001 AWE
    33498 000001
    33498 000001 QWE
    33498 000001 CDD

    Here it whould add 11 rows for 33689 and copy the data from ABC to CDD from column 3(including blank cells). Below is the output expected

    33498 000001 ABC
    33498 000001 PQR
    33498 000001 MNB
    33498 000001
    33498 000001 ASD
    33498 000001 AZXC
    33498 000001
    33498 000001 AWE
    33498 000001
    33498 000001 QWE
    33498 000001 CDD
    33689 000001 ABC
    33689 000001 PQR
    33689 000001 MNB
    33689 000001
    33689 000001 ASD
    33689 000001 AZXC
    33689 000001
    33689 000001 AWE
    33689 000001
    33689 000001 QWE
    33689 000001 CDD
    33456 000001 ABC
    33456 000001 PQR
    33456 000001 MNB
    33456 000001
    33456 000001 ASD
    33456 000001 AZXC
    33456 000001
    33456 000001 AWE
    33456 000001
    33456 000001 QWE
    33456 000001 CDD

  14. JR July 2, 2016 at 6:52 AM - Reply


    Good day!

    I would like to ask help on how to create a macro on my data sheet.

    I have a list of company names in column A and then I need to insert 50 rows after each name because I going to insert 51 state jurisdiction in column E to be able to search each name in each state.

    Column A (ENTITY NAMES) B C D Column E (State search)

    Microsemi Storage Solutions, Inc. AK
    Microsemi Storage Solutions, Inc. AL
    Microsemi Storage Solutions, Inc. WY
    PMC-Sierra US, Inc. AK
    PMC-Sierra US, Inc. AL
    PMC-Sierra US, Inc. AR
    Wintegra, Inc.

    Thank you so much..

  15. Kiran August 1, 2016 at 3:44 AM - Reply

    I have following table


    need to convert as below


    Can anyone share code for this….note the row count is changing every time you insert


  16. Rahul August 9, 2016 at 3:48 PM - Reply

    Hi PNRao,

    I want to add Row on specified sheet. number of row to inserted is mention in Cell ( for example: E10) of instruction sheet. when i run macro than this macro should enter number of row mention in cell E10 in sheet name XYZ.
    I have two worksheet. i will make separate macro for each sheet to do same thing. as both sheet have different figure and 1st i need to insert on one sheet after getting other data than i need to add raw sheet to other sheet.

    • PNRao August 14, 2016 at 11:46 PM - Reply

      The below VBA code will, insert the number of rows specified:

      Sub sbInsertRowsSpeccifiedNumberInARange()
      targetSht = "SheetName" 'Your target sheet name to insert Rows
      targetStartRow = 10 'Rows will be inserted from here in your taget sheet
      numberOfRows = Sheets("XYZ").Range("E10")
      For i = 1 To targetStartRow
      Rows(targetStartRow).Insert Shift:=xlDown
      End Sub


  17. Emilee August 20, 2016 at 2:45 AM - Reply

    I have a spreadsheet where I add a row every day and add new information into that row manually. There are also some formulas that autofill when the new line is inserted. I have a total at the very bottom of the spreadsheet. How do you change this VBA code to insert a line just above the total as opposed to the number it is at now “301”? The way the code is now, it always inserts a line at 301, but I want it to insert right above the total line regardless of which line it is on. I have also created other VBA formulas in the totals row which sum, count, and then a combined formula to get the average using the sum and count (all based on colors of cells). Thanks in advance for your help!

    Sub Inserting_Line()

    ‘ Inserting_Line Macro

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.AutoFill Destination:=Range(“A299:Y300”), Type:=xlFillDefault
    End Sub

Leave A Comment