Adding blank rows to an EXCEL spreadsheet

Microsoft Office excel 2007 - upgrade
January 7, 2011 at 19:38:04
Specs: Windows XP
Not being trained in EXCEL, I'm sure I am doing what I need done the "hard way." I receive spreadsheets in EXCEL with hundreds of lines of information already on them. I need to add a fixed number of BLANK ROWS beneath each line of information, and was hoping there is a single quick process or set of commands to do this "all at one time" and immediately (I am now doing it by highlighting EACH ROW on the spreadsheet, and then clicking on the "Insert" command 5 times, to add 5 blank spaces....and then proceeding to the next row, and the next, doing the same exercise for each of HUNDREDS of rows on the spreadsheet....a huge waste of time and effort. Can anyone help and advise?


See More: Adding blank rows to an EXCEL spreadsheet

Report •

January 8, 2011 at 14:35:13
You can shorten you manual method by selecting 5 rows at a time, right-clicking and choosing Insert. This will insert 5 rows at once.

To make it automatic, you can try this code:

It assumes that you have data in Column A and that each row has data in it.

Option Explicit
Sub InsertRows()
Dim nxtRw, insRws As Integer
'Set first insertion row
 nxtRw = 2
'Get number of rows from user
  insRws = Application.InputBox("How many rows do you want to insert?", _
                                "Input Rows", Type:=1)
'Quit if Cancelled
 If insRws = False Then Exit Sub
'Insert Rows
   Do While Range("A" & nxtRw) <> ""
     Rows(nxtRw & ":" & nxtRw + insRws - 1).Insert Shift:=xlDown
     nxtRw = nxtRw + insRws + 1
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question