insert 4 blank cells between each cell in a column?

[MechEng]

Hi guys,


I have a cloumn of data and I wish to insert blank cells between each enrty in the column. Is there an easy / fast way to do this? I recorded a macro with relavtive referances to insert 4 cels under the selected cells and gave it a keyboard shortcut but this is still time consuming as I must click each cell then press ctrl+g every time to insert the 4 blank cells and there are thousands of rows.


Seems fairly simple so didn't bother uplaoding a sample worksheet but if you like I will.


Basically i just need to turn;


1

2

3

4

5


into


1

blank

blank

blank

blank

2

blank

blank

blank

blank

3


etc

etc


Those are actual blanks not the word blankk btw!


Thanks in advance for any help!

 

[Luke M]

How's this?

[pre]

Sub InsertRows()
Dim LastRow As Integer
Dim SpaceCount As Integer
Dim StartRow As Integer

'How many rows to insert
SpaceCount = 4
'where is the first cell?
StartRow = 1

'Manually set this if you dont' want to do all cells
LastRow = Range("a65536").End(xlUp).Row

Application.ScreenUpdating = False
For i = LastRow To StartRow Step -1
Cells(i + 1, "A").Resize(SpaceCount, 1).EntireRow.Insert
Next
Application.ScreenUpdating = True
End Sub

[/pre]

 

[shrivallabha]

Hi Luke,


Can we not use:

LastRow = Range("a65536").End(xlUp).Row


LastRow = Range("A" & Rows.Count).End(xlUp).Row


as 2003- and 2007+ versions have different row count.

 

[MechEng]

Luke M y0u hero you've saved me so much time, thank you so much! :D

 

[Luke M]

@shrivallabha,


Correct, we could do the latter. More of a habit to just use 65536, as "most" of the time, people don't use that many rows. To be more robust, the line you gave would be better.


@MechEng

Always glad to help out a fellow mechanical engineer. =)

 

[safiyya]

Sir,

Thanks for all of you