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.

Code:
Sub sbInsertingRows()
'Inserting a Row at at Row 2
Range("A2").EntireRow.Insert
'
'Inserting 3 Rows from 3
Rows("3:5").EntireRow.Insert
End Sub
Instructions:
  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
Input:

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

Output:

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.

Code:
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
    Next
Next

End Sub
Instructions:

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

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: Excel VBATags:

Share This Story, Choose Your Platform!

43 Comments

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

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

  2. PNRao January 16, 2015 at 2:47 PM

    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
    Loop
    
    End Sub
    

    Hope this helps!
    Thanks-PNRao!

  3. Spandan February 16, 2015 at 12:03 PM

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

  4. PNRao March 2, 2015 at 6:58 PM

    Hi Spandan,

    You can loop through the columns and check if the condition (required text matches), then add insert the column.

    check this link:
    http://analysistabs.com/excel-vba/inserting-columns-worksheet/
    Thanks-PNRao!

  5. JH March 11, 2015 at 7:12 AM

    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!

  6. PNRao March 21, 2015 at 2:35 PM

    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
    
  7. David March 25, 2015 at 5:23 PM

    Hi,
    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
    Else
    rowCurrent = oWS.HPageBreaks(i).Location.Row
    oWS.Rows(rowCurrent & “:” & rowPrevious).Copy
    End If

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

    rowPrevious = rowCurrent – 1
    Next
    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.

    David

  8. Rob Garven April 7, 2015 at 4:37 AM

    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
    Rob

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

    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?

  10. Mike May 27, 2015 at 2:59 AM

    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!

    Mike

  11. jay July 18, 2015 at 1:06 PM

    must have to set object variable

  12. Simon July 30, 2015 at 7:43 PM

    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.

  13. PNRao July 30, 2015 at 9:09 PM

    Hi Simon,

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

    Thanks-PNRao

  14. Ash October 12, 2015 at 3:07 PM

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

  15. PNRao October 22, 2015 at 11:28 PM

    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.

    Regards-PNRAO

  16. Nikos November 4, 2015 at 2:54 PM

    Hi!
    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

  17. Prakash February 11, 2016 at 9:32 PM

    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

  18. FT February 24, 2016 at 2:40 AM

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

  19. usha March 1, 2016 at 2:02 PM

    Hi,

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

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

    to
    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

  20. Krishna March 29, 2016 at 9:04 PM

    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.

  21. Krishna March 30, 2016 at 3:54 PM

    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!

  22. Nayan May 10, 2016 at 12:01 AM

    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
    33689
    33456

    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

  23. JR July 2, 2016 at 6:52 AM

    Hi,

    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..

  24. Kiran August 1, 2016 at 3:44 AM

    I have following table

    header1
    row1
    row2
    row3

    need to convert as below

    header1
    row1
    header1
    row2
    header1
    row3

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

    Thanks.

  25. Rahul August 9, 2016 at 3:48 PM

    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.

  26. PNRao August 14, 2016 at 11:46 PM

    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
    Next
    
    End Sub
    

    Thanks-PNRao!

  27. Emilee August 20, 2016 at 2:45 AM

    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


    Rows(“301:301”).Select
    ActiveSheet.Unprotect
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(“A299:Y299”).Select
    Selection.AutoFill Destination:=Range(“A299:Y300”), Type:=xlFillDefault
    Range(“A299:Y300”).Select
    End Sub

  28. Gelareh Nobakht July 11, 2017 at 1:40 AM

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

  29. PNRao July 17, 2017 at 2:10 PM
    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
    
    Next
    
    End Sub
    
  30. REVA July 17, 2017 at 4:07 PM

    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?

  31. REVA July 18, 2017 at 10:05 AM

    Hi,
    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

    Range(“xlapp.ActiveCell.Row”).EntireRow.Insert

    End Sub

  32. REVA July 18, 2017 at 12:10 PM

    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.

  33. Anwesh July 25, 2017 at 10:31 PM

    Hi,
    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.

  34. PNRao July 27, 2017 at 7:16 PM

    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
    
    Next
    
    End Sub
    

    Thanks!

  35. Junn July 30, 2017 at 12:38 PM

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

  36. algae September 22, 2017 at 3:37 PM

    Hi,
    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

    End

  37. algae September 22, 2017 at 3:42 PM

    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

  38. PNRao October 1, 2017 at 1:36 PM

    Hello,

    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")
    
    Wb2.Sheets("Sheet1").Range("A1").EntireRow.Insert
    
    Wb1.Sheets("Sheet1").Range("B1:F1").Copy Destination:=Wb2.Sheets("Sheet1").Range("B1:F1")
    
    End Sub
    
  39. PNRao October 1, 2017 at 1:44 PM

    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
    Wb2.Sheets(iCntr).Range("A1").EntireRow.Insert
    
    'Now copy the data
    Wb1.Sheets(1).Range("B" & iCntr & ":F" & iCntr).Copy Destination:=Wb2.Sheets(iCntr).Range("B1:F1")
    
    Next
    
    End Sub
    
  40. Hallgrimur June 1, 2018 at 9:00 PM

    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.

  41. Jarett July 27, 2018 at 12:53 AM

    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.

  42. phil November 21, 2018 at 12:04 AM

    HI, I have a macro that inserts a new row into my table, I also have a macro that inserts a complete new table for recording absence. The trouble I have is that when inserting the new table the tables already existing get moved downwards as requested but my insert row macro for the second table no longer works as it is still referencing from the above location.

    Hope this makes sence

  43. Prasanth December 8, 2020 at 1:04 AM

    I need to Insert with the data in it ,at the end . I need to insert in the middle

Leave A Comment