Adding two rows after every existing row

[Hitch75]

Hi all

Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I


Thanks!

[JBeaucaire]

Try this out:

Please Login or Register  to view this content.

[jmhultin]

If you're looking for a lower-tech solution, try this:

In an empty column, sequentially number your existing rows. Copy that number sequence below below your original sequence, in the same column, then copy it a second time below that. Add a fraction, say .1, to the copied number sequences (not to your original sequence).

Then sort your spreadsheet by the new column. It should be sorted as 1, 1.1, 1.1, 2, 2.1, 2.1, 3, etc. Delete your sequence column, and you're there.

[JBeaucaire]

Add a fraction, say .1, to the copied number sequences (not to your original sequence).

Then sort your spreadsheet by the new column.

That's a standard manual technique and a very good suggestion, Hitch, if you prefer. However, you don't need to increment the values. Just copy the numeric sequence twice below the original sequence and sort. The blank rows will be below the original since the sort would be from the top down.

[Hitch75]

Very simple, I like it

Thanks a lot!!

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[Georgia Golfer]

Found this on page 11....

Can someone explain to me how to add the code to a worksheet?

Thanks

[JBeaucaire]

Georgia, as a rule, you should always post your own question in your own thread with an appropriately accurate title, then include IN your post links to these other threads you may be referring to.

========
Since I should probably have included these instructions anyway:

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[Georgia Golfer]

Sorry about that, will do for the future.

When I follow your instructions, then try to run the macro, it says

"invalid outside procedure"

[JBeaucaire]

Georgia, please start your own thread. Post your workbook in that thread if needed.

[myreyes]

thanks for sharing the solution! very simple and effective

[Bobneil]

Future Googler's may find the attached spreadsheet takes jmhultin's super simple solution and builds on it by making it super fast to do.

Insert as many rows as you want into your table (or just 1 column of data) in seconds. If you want to keep the macro's disabled because you don't trust the internets, just follow the instructions and then sort on column D. You'll get the same basic result, the macro just cleans up the worksheet a bit afterwards.

Thanks to jmhultin for giving such a good solution!