3

I, for the life of me, can not figure out why M$ has not provided this feature. If I want to insert 597, for the sake of argument lets say blank, new rows, all available information tells me to either

  1. Insert a row and hit F4 596 times
  2. scroll down until I find 597 empty rows and copy then insert those

Is there an easier way to do this? I am flattening out inventory and this is a major PITA. Is there a logic behind this design choice?

  • 1
    Please describe the downvote – puk Dec 10 '14 at 17:02
  • I'm not the down-voter, but maybe it's for the editorializing. – Excellll Dec 10 '14 at 17:35
  • Please accept @Excellll answer it's the best and requires no additional installations or plugins – Robert Houghton Jun 11 at 23:39
1

I wouldn't say easy, but certainly a third option is a combination of the above. Select 30 rows (say) with a key combination

Shift+Space+Page Down

Then hit F4 20 times (inserts 600 I agree) but far easier than doing both the others.

Another option is vba, as below with the (1) being replaced by another column, so you enter the number in a cell and insert that many rows

ActiveCell.Offset(1).EntireRow.Insert

Another alternative is to buy http://www.extendoffice.com/order/kutools-for-excel.html which has a neat option for this and many other useful purposes.

| improve this answer | |
  • Probably easier and more cost effective to just hire another bookkeeper – puk Dec 10 '14 at 16:56
1

Another option could be to use a plugin to give you added functionality. ASAP Utilities offers this:

enter image description here

I was an active user of ASAP Utilities for a number of years and found it very useful for certain laborious tasks like this one. When I migrated to a new laptop several years ago I never reinstalled it (because my work required less repetitive stuff) so not sure how good the product is these days.

| improve this answer | |
1

You can select 597 rows below where you want them entered, right click the selection, and choose Insert. This will insert 597 new rows above your selection.

If you want an easy way of selecting 597 rows, I suggest using the Name Box, highlighted in the screenshot below.

enter image description here

For example, if you want to insert 597 rows beneath row 10, type 11:607 in the Name Box and press Enter. This will select those rows. Then, you can right-click the selection and choose Insert. Alternatively, you can insert the rows via keyboard by pressing Alt > H > I > R.

| improve this answer | |
  • Yes, but selecting rows is not easy: Sometimes SHIFT acts wonky, with filters it is difficult to navigate to empty lines, go down past the bottom is slow, Page Down/Up is very sensitive... – puk Dec 10 '14 at 16:55
0

How to Insert Multiple Rows in Excel

The solution below seems to be easiest way to insert a large number of rows.

For other solutions see the linked source article.

Method 4 –Programmatically inserting multiple rows in excel:

Although this method is a bit complex than the first three, but still this can be used if you are more inclined towards the coding side. Follow the below steps to use this method:

  • Navigate to the ‘View’ Tab on the top ribbon, click on the ‘Macros’ button.
  • Now type the name for the macro say “Insert_Lines” (without quotes) and hit the create button.
  • Next, a VBA editor will be opened, simply paste the below macro code after the first line.
    Dim CurrentSheet As Object
    ' Loop through all selected sheets.
    For Each CurrentSheet In ActiveWindow.SelectedSheets
    ' Insert 5 rows at top of each sheet.
    CurrentSheet.Range("A20:A69").EntireRow.Insert
    Next CurrentSheet 

Inserting Multiple Rows Macro

  • Now comes the important thing, in the above macro the range is (“A20:A69”). - The first parameter i.e. A20 tells the position from where you wish to insert the rows and the second parameter .i.e. A69 is the number of rows to be inserted added with the start position and subtracted by 1
  • If you want to insert 50 rows starting from A20 then second parameter of range should be (50+20-1), so the range will be (“A20:A69”))
  • After adding the code you can press the “F5” key and the code will insert the required rows.
  • This macro is referenced from the Microsoft article: http://support.microsoft.com/kb/291305

Source How to Insert Multiple Rows in Excel

| improve this answer | |
0
Sub insertRows()
    Dim i As Long
    Dim j As Variant
    j = InputBox("How many rows would you like to insert?", "Insert Rows")
    If j = "" Then
        j = 1
    End If
    For i = 1 To j
        ActiveCell.Rows.EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
End Sub

This will open an InputBox where you can specify the number of rows to insert. By default, it will insert 1 if nothing is entered (there is no other error-handling). It starts at the current row of the cell that is selected, and inserts rows on a loop until the specified number have been inserted.

| improve this answer | |
  • Surrounding your code by some explanation would really improve the value of your answer. – zx485 Jan 10 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.