How can I add rows inbetween?

D

dhjacha

Pleaseeee help me! I have a worksheet with rows from 1 till 1300 and i need
to add
a 3 rows in between each data row.
I mean if i have
1 Data
2 Data
3 Data
And I want
1 Data
2Blank
3Blank
4Blank
5 Data
6Blank
7Blank
8Blank
9 Data

How do I do, please I really need this and am to looser in excel, how can i
make this easy way.

Thanks so much
 
A

Art

Try the following macro:

Sub temp()
Dim x As Long
Dim i As Long
Dim s As String
Sheets("Sheet1").Activate
x = Cells(65536, 1).End(xlUp).Row
For i = x To 1 Step -1
s = i & ":" & i + 2
Rows(s).Insert
Next i
End Sub

This assumes your sheet is called "Sheet1" -- otherwise change the code.
You can put this macro in a new module and just run it.

Art
 
S

soxcpa

If you don't want to use a macro, you could select a cell, choose
insert-row.

Then use F4 (repeat last command) to do it again. Arrow down,
F4-F4...repeat until bored.
 
B

Bob Phillips

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).row
For i = iLastRow To 1 Step -1
Rows(i).Resize(3).Insert
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Max

One play to try ..

Assume source data in Sheet1, cols A to C, from row1 down

In Sheet2,

Put in A1:
=IF(MOD(ROWS($A$1:A1)-1,4)=0,OFFSET(Sheet1!A$1,INT((ROWS($A$1:A1)-1)/4),MOD(
ROWS($A$1:A1)-1,4)),"")

Copy A1 across to C1, fill down until zeros appear
signalling exhaustion of data extract

If desired, to freeze the values / kill the formulas:
Select cols A to C, then do an "in-place":
Copy > Paste special > Check "Values" > OK
 
M

Max

A slight revision to the earlier formula (removed unnecessary part):
In Sheet2,
Put in A1:
Click to expand...

=IF(MOD(ROWS($A$1:A1)-1,4)=0,OFFSET(Sheet1!A$1,INT((ROWS($A$1:A1)-1)/4),),""
)
 
K

Ken Wright

With your data in say A1:A100, in B1 put 1 and then in B2 put =B1+3 and copy
down to B400 (ie 4 times as many rows as you currently have).

Copy Col B and then paste special as values.

Select A1:B400 and sort ascending on Col B.

Delete Col B

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
Top