Inserting blank rows

[PL]

Hi

I have 10 consecutive rows of data. I wish to insert a blank between each of
these 10 rows, is there a formula or method I can use to do this quickly? I
don't wish to manually insert the rows.

Thanks.

 

[Jacob Skaria]

Try the below
--Suppose you have data in ColA and ColB. Use a helper column say Col C
--Enter 1 to 10 in C1:C10 and copy 1 to 10 to C11:C20
--Select the data in ColA to C. Sort Col C in ascending
--Delete Column C

 

[Per Jessen]

H

In an unused column enter the numbers 1,3,5 down to last data row.
Below enter the numbers 2,4,6... representing empty rows to be inserted.

Select the entire table, and sort (Goto Data > Sort) on the column with
numbers just inserted.

Hopes this helps.
....
Per

 

[Jan Karel Pieterse]

Hi Pl,

I have 10 consecutive rows of data. I wish to insert a blank between each of
these 10 rows, is there a formula or method I can use to do this quickly? I
don't wish to manually insert the rows.

Next to your data, insert the numbers 1,2,3,... (enter 1 in row 2, 2 in row 3,
select both 1 and 2 and drag down using the fill handle).
In the first empty cell, enter =A2+0.5 (cell A2 was the first cell with a
number). Drag this formula down just as many rows as the data spans so you end
with a number 0.5 higher than the number of rows with data.

Now sort your range on the new column (select all cells to be sorted!).
Remove the temporary column.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Don Guillett]

This will work for any range selected

Option Explicit
Sub insertblankrowsinselection()
Dim fr As Long
Dim lr As Long
Dim i As Long

With Selection
Dim x As Long, y As Long
fr = .Rows(1).Row
lr = .Rows.Count + fr - 1
For i = lr To fr Step -1
Rows(i + 1).Insert
Next i
End With
End Sub