Insert 4 Blank Rows in After Each Line

[feroz_bilal]

Hello,
I have data like this

a

b

c

d

e

f

g

h

i

j

<tbody>
</tbody>

And What I want to have is:

I would like to insert 4 blank lines after each line like this:

a

b

c

<tbody>
</tbody>

and so on....

Help will be highly appreciated.
Regards

 

[kalak]

@Rick

No, I wasn't implying your code would be slow.

I guess I was implying something like that it wouldn't work with a lot of rows.
I just looked at your use of the transpose operation, which won't work with Excel at least up to 2007 if more than about 65k rows.

So I tried your posted code on test data generated by a simple code
[a1].resize(x)="X"
with varying row numbers x, and timed our posted codes.
x=16000, Rick code 0.18 secs, kalak code 0.08 secs
x=20000, Rick code runtime error '13', kalak code 0.09 secs
x=200000, Rick code 0.10 secs, kalak code 0.58 secs

Interesting that your code seemed to work, and very very fast, with 200,000 rows but gave the error with 20,000. I'd expected another warning error message. However looking down the rows it seemed your code only produced gaps to about row 13564 and no gaps for the 182,000 or so after that.

I think your code was good for what it did, being both concise and fast.

But I have to ask, did you check that your code did in fact handle the 200,000 rows?

 

[Rick Rothstein]

@Rick

No, I wasn't implying your code would be slow.

I guess I was implying something like that it wouldn't work with a lot of rows.

You are absolutely right... my code does not work correctly (so it should be ignored and not considered)!

Thank you for pointing that out for me.

 

[feroz_bilal]

Hello,
is there a way to add 3 blank rows after 2 rows, like this:

1
2
-
-
-
3
4
-
-
-

Regards

 

[kalak]

Hello,
is there a way to add 3 blank rows after 2 rows, like this:

1
2
-
-
-
3
4
-
-
-

Regards

Like this?

Sub expand()

Dim a, b(), i, lr, x
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Columns(1).Resize(lr + 1)
ReDim b(1 To 8 * lr, 1 To 1)

For i = 1 To lr Step 2
    x = x + 5
    b(x - 4, 1) = a(i, 1)
    b(x - 3, 1) = a(i + 1, 1)
Next i

Cells(1).Resize(x) = b

End Sub