Jun
13

Fastest ways to insert multiple rows in Excel

by | updated on

Inserting multiple rows in Excel may be one of the numerous tasks you come across daily. In today's article, I hope to inspire shortcut-oriented users by showing some really quick ways to add new rows in Excel. You'll also see how to solve this task using standard menus and Ribbon buttons and how to add empty rows between multiple data lines.

If you work in Excel actively, you know that most of the tables are constantly changing. Very often, they get modified when you add new details and as a result insert multiple empty rows for them. If you add rows below or above certain data in your spreadsheets every now and then, the standard Insert command looks like the most evident solution. However if pasting blank lines is your day-to-day or even hour-to-hour routine in Excel, insert-row shortcuts are by far more effective.

This article will be useful both for the shortcut people and for users who prefer standard Excel options located on the Ribbon and within different menu lists. You will find several solutions how to insert new rows in Excel with shortcuts and learn how to add blank rows between existing lines with data.

Insert multiple rows in Excel using the standard menu options

Below you'll find the most obvious ways for pasting blank rows which is employing the Insert functionality.

  1. Pick one or several rows where the blanks will appear. To do this, select the destination cells and use the Shift + Space shortcut to turn them into rows.
    Use Shift + Space shortcut to select entire rows

    Tip. You can also select entire lines using the row number buttons. You'll see the number of the highlighted rows next to the last button.
    Select entire lines using the row number buttons
  2. Go to the Home tab in Excel and click on the Insert icon.
    Click on the Insert icon to add multiple rows in Excel

    You will see your table in Excel with the rows inserted below the necessary line.
    See your table in Excel with the rows inserted below the necessary line

You can get the same result if you use the Insert menu option. Please see the steps below.

  1. Select the cells where the empty rows need to appear and press Shift + Space.
  2. When you pick the correct number of rows, right-click within the selection and choose the Insert option from the menu list.
    Choose the Insert option from the menu list

    Tip. If your cells contain any formatting, use the Insert Options icon to match the format.
    Use the Insert Options icon to match the format

Again, you'll see multiple rows inserted in your table in Excel. Now you can enter the necessary details to get your report ready.

Excel - Insert row shortcuts to speed up pasting blanks

If you think that the ways described above are fast enough, check out the below options to see what is really quick. I'll share how to insert new rows in Excel with keyboard shortcuts.

The first shortcut I'd like to cover is the one repeating the Ribbon option Insert Sheet Rows.

  1. Select the necessary number of rows where the blank lines will appear by picking the corresponding cells and pressing Shift + Space. The current content will be moved down to make room for the new rows.
    Use Shift + Space shortcut to select entire rows
  2. Then press Alt + I. Then, holding the Alt button press R.
    Press Alt+I then, holding the Alt button press R

Voila! You can see the new rows added below. Please keep reading - the most interesting details are ahead.
See your table in Excel with the rows inserted below the necessary line

Use numerical keypad shortcut to add rows in Excel

Even if you don't enter large amounts of numeric data, you can still benefit from using the number pad. The Excel insert row shortcut I show below will work only if you press the Plus key on the numerical keypad.

  1. Select the range in Excel to insert a new row. To do this left-click on the row number button next to the fist cell of the selection and extend the range keeping the left mouse button pressed.
  2. Now press Ctrl + Plus on the numerical pad.
    Press Ctrl + Plus on the numerical pad

    If you prefer to use the main keyboard, you can get the same results if you use the Ctrl + Shift + Plus on the main pad.
    Use the Ctrl + Shift + Plus on the main pad

    Tip. If you need to add numerous rows at a time, like one or two hundred, take the advantage of the F4 button. It repeats your last action. For example, if you want to insert 100 empty rows, select a range with 10 rows, use the shortcut you like to insert the blanks and then just press F4 ten times.

Special shortcut to insert rows in Excel if there's data to the right of your table

Ctrl + Plus hotkey is fast and reliable, but if you have data to the right of your main table like on the screenshot below, it may insert blanks where you wouldn't like them to be and break the structure.
Your table may have data to the right, where you wouldn't like to insert the blanks

If that's your case, in this part you'll find a solution for inserting multiple new rows in your Excel table and keeping the structure of the data next to your list as is.

  1. Format your data as Excel Table using the shortcut Ctrl + T, or go to the Home tab -> Format as Table button and pick the style that suites you best.
    Use this icon to convert a range of cells to a table with its style

    You will see the Create Table dialog box that will help you select the necessary range.
    Use the Create Table dialog box to select the necessary range

    That's how your data look like after being formatted as Excel Table:
    See your data formatted as Table

  2. Now that your list is formatted, select a range within your table.
    Select a range within your data table
  3. Hold the Alt key, first press H, then press I and finally - A. This is a shortcut for the option Insert Table Rows Above.
    Use the shortcut for the option Insert Table Rows Above

    Tip. You can achieve the same result if you select the necessary range and press Ctrl + Plus on the numerical keypad.

As you can see, new rows didn't appear between the rows to the right:
Select a range within your data table

Add empty rows between multiple data lines in Excel

Suppose you have a report in Excel and need to insert a blank line between each of the existing rows in your table. There are two ways to solve this task - the first will work for relatively small lists and the second - for bigger ones.

If your spreadsheet is not so large, look at the steps below:

  1. Keep the Ctrl key pressed and manually select each row with data by clicking on the row number.
    Keep the Ctrl key pressed and manually select each row
  2. Press the Insert button on the Ribbon or use any Excel shortcut I listed above to see the results.
    Press the Insert icon to see the results

The second option will suite better if you have a big data table.

  1. Create a helper column. Enter 1 and 2 in the starting cells, grab the fill handle and drag it to the last data cell.
    Drag the fill handle to the last data cell
  2. Now copy the series in the helper column and paste the range just below the last cell.
    Copy and paste the series in the helper column
  3. Select the entire table, go to the Data tab in Excel and press the Sort button.
    Go to the Data tab in Excel and press the Sort button
  4. On the window that will appear choose to sort by your Helper column (in my example its column D) -> Values -> Smallest to Largest.
    Select the necessary settings on the Sort window
  5. Click OK and see the results. Empty rows will appear between the lines with data.
    See empty rows appear between the rows with data

Now you can delete the helper column.

That's it! You learned several shortcuts to insert multiple rows in Excel 2016, 2013, 2010 and other versions. Now you know all the fastest ways for adding blank rows to your data. I'll readily answer any questions you may have. Feel free to post your query below. Be happy and excel in Excel!

You may also be interested in

123 comments to "Fastest ways to insert multiple rows in Excel"

  2. Karl Cartwright says:
    December 28, 2021 at 8:36 pm

    Thank you for this tip So helpful! Lots of good info here. Ctrl-Click really turned on a light bulb.

    I had a person ask me about adding 2 rows after every two rows. With this information and a little test, we found a pretty easy way to do it.

    Leave the first two rows alone
    Select the 3rd Row
    Shift Select the 4th row
    then alternate Ctrl-Click and Shift-Click all the way down
    Right-click and insert. Like magic.

    Reply
  4. Mohammed Niyas K says:
    July 19, 2021 at 4:14 pm

    If I had a shortcut to 'THANK YOU' so much that you have thought the shortcut, then I would have delivered so much 'THANK YOU's here which would have even caused this website to go down with the abundance of 'THANK YOU's.... :)

    Reply
  7. Vashti Ali says:
    November 29, 2020 at 2:20 am

    I am new to Excel
    I am working on an Excel spreadsheet
    I inserted some lines and the formulas wasn’t copied. Formulas are missing from some of my existing lines. How do I get the formulas to all to all the lines

    Reply
  9. ANF says:
    August 28, 2020 at 3:50 pm

    Once again, Ablebits saves me a HUGE amount of work. I often have to work with ginormous spreadsheets - and your product saves me so much time and effort. Thanks. Suggestion - at some point create a tool that will do just what this is article is all about. I know about the empty row removal tool (wonderful) but a row add tool would be great too, especially with large numbers of rows. The info in this article is helpful too.

    Reply
  10. Sarah says:
    April 16, 2020 at 2:04 pm

    HI.I need to copy 2 lines which have some data and insert between every line of data. Is there a way to do this rather than copying and pasting up to 700 times? The spreadsheet can vary in length from 10 to 700 lines of data to insert these 2 lines between. Thanks

    Reply
  11. Mangla says:
    January 9, 2020 at 5:38 am

    Hi, I would like to request your help on how to insert a rows between two rows which is not in an order for example one new row after data in the row number 3, than again after the date date in row number 10 and again after the data in the row number 40 and so on which is not in a definite pattern. It is very difficult to get this then the data is too big more than 10K+ rows. Is there any way it could be handled in a simple way... Thank you!!

    Reply
  12. Naman Shah says:
    November 14, 2019 at 6:17 am

    Hi,
    I would like to request your help on how to insert a row immediately after the above row is filled with some value. Do you have some kind of formula.

    Thanks

    Reply
  13. PREM says:
    October 29, 2019 at 9:00 am

    i have data in multiple columns. i want to transpose them in single column and multiple NEW rows.
    Like :-
    Dealer model
    ram a1 39 27 20 39 46 12 17
    kumar b1 46 22 21 29 33 33 37
    abhi c1 44 16 48 29 28 31 30
    rohit d1 28 50 45 49 18 20 45

    RESULT:-

    Dealer model
    ram a1 39
    ram a1 27
    ram a1 20
    ram a1 39
    kumar b1 46
    kumar b1 22
    kumar b1 21
    kumar b1 29
    kumar b1 33
    abhi c1 44
    abhi c1 16
    abhi c1 48
    abhi c1 29
    abhi c1 28
    abhi c1 31
    rohit d1 28
    rohit d1 50
    rohit d1 45
    rohit d1 49
    rohit d1 18
    rohit d1 20
    rohit d1 45

    Can you help ??

    Reply
  14. Christian says:
    October 10, 2019 at 2:08 pm

    I have a 30k line spreadsheet which I have sorted by column T. I would like to insert a blank row after every time the value in column T changes. On this blank line I would like to auto-sum the values in column S. Can you help?

    Reply
  15. James Norquist says:
    September 23, 2019 at 6:11 pm

    I would like to insert a new line in my Excel Spreadsheet. I would like line 6 to appear twice, one with the name "Robert Robinson" one with the name "Sheryl Robinson", his spouse. All other data would remain the same. I don't want Sheryl's information to be on line 7; I want two line 6s. Can you help me? Thank you!

    Reply
  16. James says:
    September 23, 2019 at 6:10 pm

    I would like to insert a new line in my Excel Spreadsheet. I would like line 6 to appear twice, one with the name "Robert Robinson" one with the name "Sheryl Robinson", his spouse. All other data would remain the same. I don't want Sheryl's information to be on line 7; I want two line 6s. Can you help me? Thank you!

    Reply
  18. Karen says:
    July 3, 2019 at 1:17 pm

    Question. I have a spreadsheet with data that needs to be made into a chart. However, they would like very obvious spacing between some of the data so it looks like three separate data sets in one chart. The only way we could think of to do this was to add a blank column between the columns we wanted separate. The problem is when you show the Data Table, there are now black columns in that too. So, is there a way to do this so it doesn't look silly? Either adding space between columns (padding didn't work) or manually removing the empty column from the Data Table.

    Reply
  22. Migdalia says:
    June 6, 2019 at 4:04 pm

    I need to add a row in between which I now how to do it if these are blank rows.
    But...i need to add a row in between, all with the letter C in it (not blank rows).
    Please HELP!

    Reply
  24. Lizzi Tremayne says:
    March 25, 2019 at 6:27 pm

    You guys are awesome. My life and multiple career spreadsheets are little, but still imp to me. :)
    Thanks for making my day a whole lot better.
    xx
    Lizzi Tremayne Author

    Reply
  25. Ashwani Agarwal says:
    March 9, 2019 at 1:04 pm

    I seek to insert as many no of rows as mentioned in Quantity column in a list (sample of which) as referred below
    Asset Cap-date Use Asset description Quantity
    100010 20-06-2014 94 SEZ Land 50
    200001 18-01-2002 60 Helipad and pathway at sit 1
    200004 09-04-2004 60 Drains 60

    How I can automate insertion of exact no of rows as mentioned in quantity column underlying each row.
    Thanks

    Reply
  26. Ghizali says:
    January 16, 2019 at 9:47 am

    Hi,

    Can you please tell me how to add row(s) after a gap of rows in a large database in a go.

    E.g. I need to add a row in a large database after every 10th row. How to add ?

    Regards,
    Ghizali Ahmed

    Reply
  30. Daniel Ko Ye says:
    October 23, 2018 at 5:06 am

    Hi Maria,
    This is very helpful.
    How can I do If I do not want to insert new row all.
    I want to insert new row for certain cells.
    For instance, I have 100,000 items. And I want to insert new row under 300 items.
    How do I do that?
    Thanks in advance.

    Reply
  31. Himself says:
    August 23, 2018 at 10:39 pm

    please I have more than 500,000 data in a single column. I want the content in each column to be repeated 6x and an empty column to separate or mark the begin of each column...
    please how can I do this? it is seriously giving me headache, I can't go through the stress of doing it manually...
    thanks in advance.

    Reply
  32. Elly-Anne Ehrman says:
    July 23, 2018 at 9:32 pm

    I've created an Excel workbook that has tabs for each month of the year and timesheets on each monthly worksheet for each employee. They include different projects and in alternating columns(one column for each date has the number of hours, the next column to the right of it has a letter to indicate the phase. What I would like to do is calculate for each project, how much time is being spent on each phase for the entire year.What's the best way to do this?

    Reply
  33. Elly-Anne Ehrman says:
    July 23, 2018 at 9:30 pm

    I've created an Excel workbook that has tabs for each month of the year and several timesheets on each monthly worksheet for each employee. The timesheets include different projects and in alternating columns(one column has the number of hours, the next column to the right has a letter to signify the phase. What I would like to do is calculate for each project, how much time is being spent on each phase for the entire year. I'm not sure how to go about this. Is this something a Pivot Table would be suitable for?

    Reply
  35. nicolas says:
    April 13, 2018 at 10:04 am

    Hi , I am working with a large dataset & want to insert 10 blank rows between each row. I tried to manipulate the vba code posted by Alexander as another poster seemed to have a similar problem, but I can't get it to work! Please help (code I tried to manipulate below):
    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

    Please advise! Thanks :-)

    Reply
  37. Mary Queved says:
    February 16, 2018 at 10:06 pm

    Hello,
    I'm formatting a spreadsheet using List Data Validation in some cells and things like Date formatting in other cells for quicker fill out. This spreadsheet data will grow every time by adding a new row of information. Is there a way to automatically insert a new formatted row once a new row of information is filled? I always want to have just one empty formatted row below my last filled-out row without having to copy and paste row formatting every time.

    Thank you very much!

    Reply
  38. I MAPAKO says:
    February 5, 2018 at 1:10 pm

    How do I insert blank rows after rows of each product when rows of data are different per product in a series of many products?
    Product no Description Price
    00001 SR1500 Gl77 1000.00
    00001 SR1530 LGA2 50.00
    00001 SR1250 GHV1 500.00
    00002 SR1325 BNJ3 1200.00
    00002 SR2001 NBD4 1356.00
    00003 SR2658 MNJ1 4589.00
    00003 SR1236 NBH9 1254.00

    Reply
  41. Jaydeep says:
    November 27, 2017 at 1:27 pm

    Hello,

    I am preparing packing list in excel. For the details of every 1 container i need 1 row in my format of packing list. So if i entered the details for number of containers, rows should be automatically added or deleted as per my requirement. Can it be happen?

    Reply
    • Gennady Terekhov (Ablebits Team) says:
      January 17, 2018 at 11:51 am

      Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

      Reply
  49. Temitope says:
    June 12, 2017 at 11:36 am

    WOW,

    Thank you this worked for me,
    "2.Now copy the series in the helper column and paste the range just below the last cell."

    I have been using different trial versions of software.
    but this has solved it.

    Thanks

    Reply
  50. Kristen says:
    May 8, 2017 at 4:07 pm

    I have the following formula in my spreadsheet. I want to add 5 blank columns below - I'm trying to convert a horizontall organized spreadsheet into more vertical format.

    Family name spouse (2 names vertically stacked) Children (up to 5 vertically stacked)

    Here is the formula -

    =OFFSET('All Members Master'!E4,(ROW(A1)-1)*1,0,1,1)

    The family name needs 4 blank spaces below it to compensate for the space for up to 5 children.

    I'm pretty good with formulas but haven't used macros before.

    Thanks in advance.

    Reply
  53. Cyndi says:
    February 10, 2017 at 3:08 pm

    Can anyone tell me how I can take the data below and create multiple rows for each column for the unique number. For Instance I need 3 rows for unique number 123, a row with Number, a row with current, and a row with amount.

    Unique Number Current Amount
    123 381.43 125.87 114.43
    1234 50.37 55.41 50.37
    12345 40.26 55.41 50.37
    123456 44.8 59.29 0
    234 647.31 194.19 176.54
    2345 324.71 78.77 71.61
    23456 324.71 238.7 71.61
    234567 549.01 78.77 71.61
    345 549.01 238.7 71.61
    3456 42.99 65.71 59.74
    34567 385.95 84.91 77.19
    345678 385.95 308.76 77.19
    456 0 84.91 77.19
    4567 0 308.76 77.19
    45678 119.47 65.71 59.74

    Reply
  54. Hamu says:
    January 26, 2017 at 11:38 pm

    Hi I know this is a very old thread. Is it still alive??

    Thanks for your article! My question is how can I insert or delete a row and the other data maintain its numerical order. For example, if I insert a new row between numbered rows 5 and 6, how can I make sure that the new data is now numbered #5 and all of the subsequent or following data shifts up by one space and reflects its new position? So, in this case what used to be #5 in the list is now numbered #6?

    Reply
  56. Zulqar Nain says:
    January 7, 2017 at 7:58 am

    Thank you so much dear. 2nd method of entering rows multiple was much of a help. otherwise I would have wasted ma whole day to do that.

    Reply
  62. Shabkar says:
    June 17, 2016 at 3:18 pm

    I want insert at different location in data execel sheet. Insert row must be a particular named down row.

    Reply
  63. PATRICIA A TAVERAS says:
    May 27, 2016 at 3:46 pm

    I neeD to add two rows but no empty rows. something like this:

    Site Name Class Name Participant Name
    Childplus CLEARWATER CENTER 4A CLEARWATER Ambrocio, Natalie
    ELC
    DIF
    Childplus CLEARWATER CENTER 4A CLEARWATER Cordes, ALan
    ELC
    DIF
    Childplus DUNEDIN CENTER 4B DUNEDIN Lopez, Carlos
    ELC
    DIF

    Reply
  66. James says:
    April 13, 2016 at 6:59 pm

    Hi,

    I have a list of accounts and month end dates, below is a sample:

    account end_date return
    xxx-xxx-xxxx-1 Friday, May 31, 2013 -0.67
    xxx-xxx-xxxx-1 Sunday, June 30, 2013 -0.36
    xxx-xxx-xxxx-1 Wednesday, July 31, 2013 3.44
    xxx-xxx-xxxx-1 Saturday, August 31, 2013 -2.23
    xxx-xxx-xxxx-1 Monday, September 30, 2013 2.89

    I am trying to insert rows for all of the days between each month end date. For example I want to insert a new row for June 1, June 2, June 3.... June 29. Can anyone help me out? Thanks.

    Reply
  67. Christian Mendoza says:
    March 11, 2016 at 1:17 am

    How do I create a series between each blank row I want to insert numbers 1 to 100 but I don't want to add each individually is there a short cut?

    Reply
    • Fedor Shihantsov (Ablebits Team) says:
      March 31, 2016 at 2:42 pm

      Hello Christian,

      You can select multiple areas and fill it with numbers using the following macro:

      Sub FillSelectedAreas()
      Dim area As Range
      Dim cell As Range
      Dim i As Integer
      For Each area In Selection.Areas
      i = 1
      For Each cell In area
      cell.Value = i
      i = i + 1
      If i > 100 Then
      Exit Sub
      End If
      Next
      Next
      End Sub

      Reply
  69. Angela says:
    February 1, 2016 at 10:12 pm

    Hello. I have a spreadsheet that needs to have data entered every 6 months for many specific items. Is there an easy way to add a row after the last entry for each item in order to make room to add the newest data or do I have to manually insert a row every time for each item?
    Ex:
    A B C
    OW-1 7/22/14 36.21
    1/19/15 37.89
    7/27/15 43.87
    OW-2 7/22/14 46.19
    1/19/15 44.24
    7/27/15 51.89
    OW-3 7/22/14 35.14
    1/19/15 32.13
    7/27/15 36.57

    Reply
    • Angela says:
      February 1, 2016 at 10:16 pm

      After posting my question, the format of the example shifted all of my data to to be left aligned. The item identifiers are in column A, dates in column B and data in column C (Note: the item identifier is only listed once at the first entry, therefore the proceeding rows only have data in column B and C.)

      Reply
    • Fedor Shihantsov (Ablebits Team) says:
      March 31, 2016 at 2:16 pm

      Hello Angela,

      I recommend you find and select all cells that contains "OW" (hit Ctrl+F shortcut).

      Then run the following macro to select entire rows of selected cells:

      Sub SelectEntireRows()
      Selection.EntireRow.Select
      End Sub

      Now, when the row selected, you can use the insert function described above.

      Reply
  70. Deirdre says:
    January 27, 2016 at 5:56 pm

    I'm trying to download data to Excel 2010 but it stops and gives message that there's too much data. It is only allowing the 65K some rows. How do I get all my download into into one file? Thanx!!!!

    Reply
  71. Dave says:
    January 22, 2016 at 3:26 pm

    It's always nice to find a solution that's both simple and innovative.

    I coded something before I read this article but your solution was much smarter.

    Thanks

    Reply
  73. Dave says:
    June 25, 2015 at 11:59 am

    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

    Reply
  74. Dave says:
    June 22, 2015 at 12:01 pm

    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

    Reply
  77. Yogesh Gurung says:
    April 17, 2015 at 1:01 pm

    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

    Reply
  78. Pavithra says:
    April 15, 2015 at 10:38 am

    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.

    Reply
  82. Sabina says:
    March 12, 2015 at 9:52 pm

    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.

    Reply
  87. Beckie says:
    December 3, 2014 at 2:36 pm

    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,

    Reply
  89. Don says:
    December 1, 2014 at 2:52 pm

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

    Reply
    • will says:
      January 13, 2015 at 12:59 pm

      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

      Reply
      • Cam says:
        July 5, 2019 at 12:01 am

        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.

        Reply
  90. Jenny says:
    November 15, 2014 at 7:29 pm

    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.

    Reply
  91. Anonymous says:
    November 14, 2014 at 11:00 am

    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.

    Reply
    • Alexander says:
      November 14, 2014 at 4:27 pm

      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/2013/12/06/add-run-vba-macro-excel/

      Reply
      • Julaayi says:
        July 8, 2016 at 2:52 pm

        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.

        Reply
  92. Anonymous says:
    November 12, 2014 at 7:13 pm

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

    Reply
  93. Jaheer Hussain says:
    November 7, 2014 at 9:48 am

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

    REgards,
    Jaheer

    Reply
  94. Hamid says:
    October 8, 2014 at 3:44 pm

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

    Reply
  95. Denise says:
    August 12, 2014 at 2:19 pm

    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

    Reply

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