LyteByte

One of the features that are missing in Excel 2007 or earlier versions (and which I always wished it was there) is that one cannot specify the number of rows or columns to be inserted directly. So the user is left with the option to insert one row or column at a time. This is fine if its only couple of rows or columns but there are some work around which will help if you are inserting large number of multiple rows or columns.

There are few methods for inserting multiple rows or columns in Excel.

Copy / Paste:

First method is more like a brute force method. Open a new spreadsheet and copy the number of rows you want to insert and in the actual spreadsheet where you want to insert, right click on the row above which you want to insert the rows and click Insert Copied Cells.

Keyboard Shortcut to Insert a row or column:

Another method is to use the Keyboard Shortcut Key F4 to insert rows. Note that you have to add at least one row using the conventional method (Right click on the row and Click Insert). Then press the function key F4 for every row or column that you want to insert. This method not only inserts the empty rows or columns but also copies the format from the previous row or column.

Note: F4 is a Shortcut Key to Repeat the last action in Excel and also it is used switch between absolute and relative reference in Formula.

Using Macro to insert multiple rows or columns in Excel:

Alternatively you can also use the following macro where you replace a1 with the row number above which you want to insert rows (Lets say we want to add 10 rows at row 215, then change a1 with a215) and replace a2 with the number of rows to be inserted added with a1 and subtract by 1 (In this case it will be 215+10-1, therefore a224). This macro is from the sample by Microsoft.

Sub Insert_Rows_Loop()
    Dim CurrentSheet As Object  

    ' Loop through all selected sheets.
    For Each CurrentSheet In ActiveWindow.SelectedSheets
        ' Insert n rows depending on values of a1 and a2.
        CurrentSheet.Range("a1:a2").EntireRow.Insert
    Next CurrentSheet
End Sub

Save this macro and whenever you want to insert, make a quick change to the numbers and run it. Make sure you have the sheet where you want to insert rows as the current active sheet when you run it.