Tip: Insert multiple rows at once

Inserting multiple rows "the Excel way"

If you want to insert multiple rows at once the standard procedure is to first select multiple rows and then use the "Insert" command.
For example if you want to insert 10 rows between A1 and A2, you first have to select rows 2:11 and then insert the rows.


Inserting multiple rows "the easy way"

The above solution works for small amount of rows. However if you need to insert for example 800 rows this is not a workable solution. Therefore I have written the following macro which lets you choose the amount of rows to insert.

Sub sbInsertMultipleRows()
' Insert multiple rows at once. Rows are inserted above the
' currently selected row/cell
      Dim lNewRows                           As Long
      Dim lCurrentRow                        As Long

      ' detect if current selection is a row or cell
      If UCase(TypeName(Selection)) <> "RANGE" Then
            MsgBox "Please select an initial row or cell to insert rows above.", _
            Exit Sub
      End If

      ' Let user choose an amount of rows to insert:
      lNewRows = Application.InputBox("Number of rows to insert", _
                                      "Insert multiple rows", 1, , , , , 1)

      ' Cancel if amount is 0 or user choose to cancel:
      If lNewRows <= 0 Then Exit Sub

      ' Insert the rows:
      Rows(Selection.Cells(1).Row & ":" & _
           Selection.Cells(1).Row + lNewRows - 1).Insert shift:=xlDown

End Sub


3 Responses to “Tip: Insert multiple rows at once”

  1. You can now also do this with ASAP Utilities.
    In the Excel menu choose ASAP Utilities » Columns & Rows » Insert multiple empty rows at once…


  2. Worked nice thanks
    excel 2003

  3. Worked Very Nice in excel 2007.
    thanks a lot.