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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

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.

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.

mongopono.ru – Inserting Rows

LIMITED TIME OFFER
By |March 28th, 2013|Excel VBA|35 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.

35 Comments

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

    I need small information .In excel every 27 rows after insert 5 rows.how 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
      Loop
      End Sub
      

      Hope this helps!
      Thanks-PNRao!

      • 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

    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

  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
    Rob

  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.

        Thanks-PNRao

  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!

    Mike

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

        Regards-PNRAO

  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

    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

  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

    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

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

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

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

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

    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.

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

      Thanks-PNRao!

  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


    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

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

    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

  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.
      Thanks,
      JUnn

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

    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.

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

      Thanks!

Leave A Comment


Related pages


how to insert a comment in excelindex formula excel 2007excel highlight duplicate cellsexcel remove duplicate cellssample project plan mppclustered column chart excel 2013excel add row shortcuthow to use multiple if statements in excelappend to a text filehow to shade cells in excelhow to create drop down menu in excel 2007advanced excel macrosisna vlookupremoving characters in excelvba query databaseproject status report template powerpointdeveloper tab in excelunhide workbook excel 2010excel vlookup example downloadvb adodb.connectionhow to use vlookup step by stepdelete repeated rows in excelclear filter vbavba rowdeveloper tab to ribbonloop function vbasum if condition in excelvba save worksheetchart wizard in excel 2010how to copy and paste excel sheets exactlyexcel project management spreadsheetvba excel replacevba procedureshow to unhide the rows in excelworkbook vbabuy excel templatescolumn width excel vbavba shapes propertiesexcel chart titlesdelete sheet rowsdbms for dummiesremove excel workbook passwordvba excel dynamic arrayvb 6.0 interview questions and answers for experiencedinsurance dashboard examplemacros in excel 2007excel userform vbagantt project excel templateestimating spreadsheetsexcel developer tab 2007ddl & dmlhow to hide numbers in exceloutlook vba code sampleshow to check duplicates in excel 2007how do i create a hyperlink in excelvba eventshow to insert a cell in excelhow do you unhide rows in excelhow to delete excel columnsswitch vbasumifs excel 2007transpose vbaexcel identify duplicate rowsexamples of ddlinsert rows in excelexcel convert to xmlexcel vlookup navba getfilenamehow to merge three columns in excelunhide columns in excel 2013select case in vbacheck mark for excelexcel unlock password protected sheetvba macro codecreate userform vbaexcel template project planexcel function vlookup