Comments on: How to insert multiple rows in Excel: shortcut, right-click, VBA

Inserting rows in Excel is a routine task that many users encounter daily. While adding a single row is relatively simple, inserting multiple rows in the right places can sometimes be a challenge. Continue reading →

Comments page 5. Total comments: 133

  1. To insert a blank row after each row that houses the word "TOTAL" down through your spreadsheet until "GRAND TOTAL"

    1) At the top (under the MACRO TITLE), type "Dim inX As Integer" (exactly as written in the quotations).

    2) At the end (between your last line in your MACRO and "END SUB", but on a different line), copy and paste the following:

    inX = 2
    Do Until Range("C" & inX) = "Grand Total"
    If Right(Range("C" & inX), 5) = "Total" Then
    Rows(inX + 1 & ":" & inX + 1).Insert Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove
    End If
    inX = inX + 1
    Loop
    End Sub

  2. I am using MS Excel v10. I am trying to step into my V.B. / Macro and add an entry that will insert a blank row after each line showing Total! Please tell me there is an easy way to do this. I am doing it manually now and when you have multiple sheets it becomes burdensome. Can you help me? Thanks, R, Dave

  3. loved th method thanks for sharing

  4. Thank you

  5. Wonderful tip, this was quite helpful for me, if you need multiple rows in between copy the additional colom as said above....suposse the additional column is filled with 1 to 5 Numbers...and now if you need to add 5 rows, just copy and paste to last cell 5 times the result will so
    Example:
    Text Additional column
    A 1.00
    b 2.00
    c 3.00
    d 4.00
    e 5.00
    1.00
    2.00
    3.00
    4.00
    5.00

    After sorting with additional column you will find the result as below-
    A 1.00
    1.00
    b 2.00
    2.00
    c 3.00
    3.00
    d 4.00
    4.00
    e 5.00
    5.00

    So,I Find this as the quickest method and helped me a lot in my daily job.
    Thanks again

  6. Fantastic job with the last trick, I struggled with it so much to insert multiple rows between data. Very very handy!

    Good job.

    Thanks a ton.

  7. Thanks.
    Very helping.

  8. Special shortcut to insert rows with copied data in Excel if there's data to the down of your table

    1. Hello, Phani,

      For us to be able to help you better, please send me a sample with your data in Excel.

  9. Hi I am trying to insert two lines after each break in name change ome of my clients I have multiple times and some only once. Like subtotaling I want to add lines after each name change? Please help.

    1. Hello, Sabina,

      Most likely you need a macro or VBA. Sorry, I cannot help you with this.

  10. You're amazing!

  11. Thanks a lot for the trick it really saved my time

  12. I would like to add 3 empty rows after each vendor that I have listed in a column, How do I do this?

    1. Hello, Lori,

      Most likely you need a macro or VBA. Sorry, I cannot help you with this.

  13. No matter what, it greets me with "Excel cannot shift non-blank cells off of the worksheet....." :-(

  14. I have an excel file in which I have 20 sheets, all with formulas that are connected to the first page. I want to be able to insert/delete lines on the first page and that line on all sheets to be inserted or deleted as well, how do I accomplish this?
    Thanks,

  15. Its really helpful

  16. Is there a way to do this when the value changes - for example, I have 8 rows with "12345", then 8 rows of "54321" then 8 more of "92939". I would like to know if I can have a macro to insert blank rows between each "group" ?

    1. I think this can work

      Sub InsertEvery8th
      Dim rowNo, rowStart, rowFinish, rowStep As Long
      Dim rng2Insert As Range
      rowStep = 8
      rowStart = Application.Selection.Cells(1, 1).Row + 1

      rowFinish = (ActiveSheet.UsedRange.SpecialCells( _
      xlCellTypeLastCell).Row * 2) - rowStart
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      For rowNo = rowStart To rowFinish Step rowStep
      ActiveSheet.Cells(rowNo, 1).EntireRow.Insert
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      End Sub

      1. Thank you x 1 Million for this. Used it in 10 files varying depending on the file every 110th, 109th, or 99th. You have to add one more number than the total set you have. So if you want a Row added for every 99 entries, the value is 100.
        It also adds a row before the first row after the header row. I'm sure this would be easy to code out, for me as a luddite it was only 10 files with 22,000 entries, so not a big deal to remove manually afterwards.

  17. Colums B&C are Merged in rows 1-23, when I insert rows in this section the merged formating does not carry over using format same as above or below. Is there a setting that can correct this? Please advise.

  18. Hi

    Can you please tell me how do i insert multiple rows in odd numbers rows ( in row # 3,5,7,9......) in huge list of data? Thanks.

    1. Please use the following macro to insert every other row from the first selected cell to the end of your data.

      Sub InsertEveryOtherRow()
      Dim rowNo, rowStart, rowFinish, rowStep As Long
      Dim rng2Insert As Range
      rowStep = 2
      rowStart = Application.Selection.Cells(1, 1).Row + 1

      rowFinish = (ActiveSheet.UsedRange.SpecialCells( _
      xlCellTypeLastCell).Row * 2) - rowStart
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      For rowNo = rowStart To rowFinish Step rowStep
      ActiveSheet.Cells(rowNo, 1).EntireRow.Insert
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      End Sub

      If you are not very good at VBA, please see this article:
      https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/

      1. Thank you. This script helped me to insert multiple blank rows in between thousands of records I have. However, I had to run the macro multiple times to achieve my results. Is there a way to have custom selection? Say for example, I have 12 vendors and I want to insert 12 blank rows between each row. It would be nice if you have a parameter in the macro that inserts the rows based on that parameter.

  19. I have a huge spreadsheet containing unique names of genes in one of the columns, and information about the genes in the subsequent columns. These gene names sometimes have similar letters in their name. Eg. genes that start with the name LRRC and usually have a numerical value after them. I am trying to select the entire row of every gene whose name starts with LRRC (which are scattered in the spreadsheet) and then copy it into the new spreadsheet. How do I do this? I've tried finding all, selecting all of the genes, but this isn't time efficient, as there are often hundreds of genes with this same name...

    1. Hello,

      Please try our Advanced Find and Replace add-in:
      Enter LRRC into the Find what field
      Click Find all
      Press Select cells and pick the option Select rows with all found entries.

  20. I m follow Add empty rows between multiple data lines in Excel sheet.
    Very quickly create format option.

    REgards,
    Jaheer

    1. Hello, Jaheer,

      Please give me more details about the problem you have.

  21. Thank you very very very much!
    god bless you!
    I was badly in trouble and was killing myself to find a way for inserting multiple blank rows in a huge list of data. thank you dude :)

    1. Thank you so much for your feedback, Hamid,

      Happy to hear you found the article helpful.

  22. I have a two page worksheet. How can I insert a new row in the first sheet but the last row in the first worksheet will automatically go down to the next page below the header in the second page? Please help

  23. I am inserting new rows to an existing spreadsheet and when I do that the row has no gridlines. Help?

    1. Hi Doreen!

      Thank you for your question. Most likely you have custom gridlines (color, size or style). For us me to be able to help you better, please email your workbook at support@ablebits.com

    2. File > Options > Advanced > Display Options for This Worksheet > Show Gridlines

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)