How do I insert a row after every row of data in XL?

[jetanddug]

In a spreadsheet containing 300 rows of data, I need to automatically insert
a blank row after every row of data. PLEASE can somebody help - it's sooo
slow doing this manually.

 

[Don Guillett]

sub insertrows
for i=cells(rows.count,"a").end(xlup).row to 1 step -1
rows(i).insert
next i
end sub

 

[Chip Pearson]

Try some code like the following. Select a range from the first row to
the last row and then run the code. It will insert a blank row below
each row in the selection.

Sub InsertRowsInSelection()
Dim StartRow As Long
Dim EndRow As Long
Dim RowNdx As Long

StartRow = Selection.Cells(1).Row
EndRow = Selection.Cells(Selection.Cells.Count).Row

For RowNdx = EndRow To StartRow Step -1
Rows(RowNdx + 1).Insert
Next RowNdx
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Old in Dallas]

It would help to know why you need to do it - but you can open another tab
(worksheet) and use formulas on every other row to equal the other
worksheet's values.

 

[Max]

Assume data starts in row2 down
Insert a new col A
Put in A2:A3 the numbers: 1,2
Select A2:A3, drag down to the last row of data to fill col A as: 1,2,3, ...
300 (say)
Copy the filled range of numbers (1-300), paste it immediately below in the
same col A. Then select the entire source range, inclusive of col A, do a
data>sort by col A, ascending. Voila, the results you seek will appear.
Delete col A.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Gord Dibben]

If just for appearance, simply double the row heights.

Blank rows will make sorting and filtering and other stuff more difficult.


Gord Dibben MS Excel MVP