MrExcel Publishing
Your One Stop for Excel Tips & Solutions

insert muliple line breaks fast


Posted by phil on January 21, 2002 5:57 PM

I need to import data from an excel speadsheet into MYOB. The data in the excel spreadsheet appears on each row down the spreadsheet. To import this data i need to have a blank row between each row of data. To do this i manually insert a row using keyboard shortcut "apple I".
this can be tedious.

Is there a faster way of inserting a blank row between each line of data?


Posted by Tom Urtis on January 21, 2002 6:19 PM

Two ways to do this, one using VBA and one not, thanks to responses on this board from previous similar requests:

VBA:
Sub InsertRows()
Dim i As Integer
Dim LastRow As Integer
LastRow = Cells(65536, 1).End(xlUp).Row
For i = LastRow To 2 Step -1
Rows(i).Insert Shift:=xlDown
Next i
End Sub


Non VBA:
(1) Number your rows (1 to 300 or whatever the last row is) in an unused column.
2. Paste a copy of these numbers (1 to 300 or whatever) directly beneath the copied numbers -- on unused rows.
3. Sort (Ascending) on the column that contains the numbers, then clear the contents (the numbers) of that column.


Any help?

Tom Urtis

Posted by Barrie Davidson on January 21, 2002 6:22 PM

Hey Tom, your non-VBA way is a neat trick! (nt)

Posted by Tom Urtis on January 21, 2002 6:25 PM

Re: Hey Tom, your non-VBA way is a neat trick! (nt)

Thanks Barrie, actually someone on this board (cannot recall who, but not me) came up with this one, so I can't take credit but it is kinda cool.

T.U.

Posted by phil on January 21, 2002 8:55 PM

works a treat - thx Two ways to do this, one using VBA and one not, thanks to responses on this board from previous similar requests: