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.




50+ Project Management Templates Pack
Excel PowerPoint Word


Advanced Project Plan & Portfolio Template
Excel Template


Ultimate Project Management Template
Excel Template


20+ Excel Project Management Pack
Excel Templates


20+ PowerPoint Project Management Pack
PowerPoint Templates


10+ MS Word Project Management Pack
Word Templates


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

Advanced Project Plan Excel Template

Related Resource External VBA Reference
By |March 28th, 2013|Excel VBA|41 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

  18. Gelareh Nobakht July 11, 2017 at 1:40 AM - Reply

    I want to insert 8 rows after each 20 row. It’s my first time using VBA in excel.

    • PNRao July 17, 2017 at 2:10 PM - Reply
      Sub sbAT_InserRowsAfterEvery20Rows()
      intStartRow = 1 'Starting row
      intAfterEveryNRows = 20 'Number of Rows to Skip
      intNumRows = 8 'Number of rows to be inserted
      intRepeatNTime = 10 'Number of times to be repeated
      For iCntr = 1 To intRepeatNTime
      startRow = intStartRow + intAfterEveryNRows * iCntr + (intNumRows * (iCntr - 1))
      endRow = intStartRow + intAfterEveryNRows * iCntr + (intNumRows * iCntr) - 1
      Rows(startRow & ":" & endRow).Insert _
      Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      End Sub
  19. REVA July 17, 2017 at 4:07 PM - Reply

    hello PNRao,
    what VBA code can i put if i want to copy down data validation rule to next cell based on the condtion that previous cell is empty or not?

  20. REVA July 18, 2017 at 10:05 AM - Reply

    I am new to VBA coding and writing macros. i am trying to insert a new row based on my cursor position. This is the code which i am tried using but it gives error like Run-time error “1004”: method ‘Range’ of object ‘_Global’ failed. please suggest how to corect it.

    Sub insertrow()

    ‘Inserting a new row at my cursor postion


    End Sub

  21. REVA July 18, 2017 at 12:10 PM - Reply

    hi, i have got solution to my previous problem and now i am bale to insert row and column at my desireed location(here cursor location). now i have one more problem which is i am not able to undo the cells created. i want to create a code so that it undo’s or brings excel to original sheet once i open it again.

    • Junn July 30, 2017 at 12:38 PM - Reply

      Hi Reva,
      What’s the new code? I am looking for the solution too.

  22. Anwesh July 25, 2017 at 10:31 PM - Reply

    I have a column that has numbers like 30, 60 ,90,120 and so on.I want a macro for inserting a row when there is difference of 120 or above. can you please help.

    • PNRao July 27, 2017 at 7:16 PM - Reply

      Here is the code to insert a row if there difference is more than a certain value:

      Sub sbInsertRowIfDifferenceMoreThanCetrainValue()
      colToCheck = 1 'This is the Column number to Check
      lastRow = 100 'This is Your Last Row with Data
      diffToCeck = 120' Difference value
      For iCntr = lastRow - 1 To 1 Step -1
      If Abs(Cells(iCntr, colToCheck) - Cells(iCntr + 1, colToCheck)) >= diffToCeck Then
      Rows(iCntr + 1).Insert
      End If
      End Sub


  23. algae September 22, 2017 at 3:37 PM - Reply

    I’m struggling with trying to copy a row from one file and insert the row in another file. Please help

    Range” file name ABC”(“B1:F1″).Copy

    Range” File name XYZ”(“A1″).EntireRow.Insert
    Range” File name XYZ”(“B1:F1”).Select
    ActiveSheet.Paste ‘
    Application.CutCopyMode = False


    • PNRao October 1, 2017 at 1:36 PM - Reply


      Here is the VBA code to copy to insert new row and paste the copied data in workbook.

      Sub sbCopyRangeAndPasteInToAnpotherFile()
      Set Wb1 = Workbooks("ABC")
      Set Wb2 = Workbooks("XYZ")
      Wb1.Sheets("Sheet1").Range("B1:F1").Copy Destination:=Wb2.Sheets("Sheet1").Range("B1:F1")
      End Sub
  24. algae September 22, 2017 at 3:42 PM - Reply

    File ABC has only one worksheet, whereas file XYZ has 56 worksheets, that is to say that range B1:F1 go to worksheet 1, range B2:F2 go to worksheet 2 and so on. Thanks

    • PNRao October 1, 2017 at 1:44 PM - Reply

      You can use a for loop and change the macro accordingly. Here is the VBA code to insert rows in each sheet of the workbook and copy the data and paste it in newly created row.

      Sub sbCopyRangeAndPasteInToAnpotherFile()
      Set Wb1 = Workbooks("Book1")
      Set Wb2 = Workbooks("Book2")
      'Loop 56 times
      For iCntr = 1 To 4
      'First Insert Row
      'Now copy the data
      Wb1.Sheets(1).Range("B" & iCntr & ":F" & iCntr).Copy Destination:=Wb2.Sheets(iCntr).Range("B1:F1")
      End Sub
  25. Hallgrimur June 1, 2018 at 9:00 PM - Reply

    I’m making a model for startup costs, construction time and profit. I’m trying to create a macro that takes a varying value input (construction time) and generates rows based on the input. The cells in the generated rows need to contain a constant divided between them equally (the startup cost). So the construction time may vary and I can’t seem to get this right.

  26. Jarett July 27, 2018 at 12:53 AM - Reply

    I am wondering if it is possible to use the information from row 1 A1 and row 2 A1 to create a different number of rows between the 2.
    what i am thinking is taking row 1 A1 information minus row 2 A1 information dived by 60 and that is the number of lines that need to be created between row A and B. And them repeat this all the way thru the document. between 2-3 thousand lines. to create a document with around 120 thousand lines.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.