Insert Multi Rows Between Existing Data [Solved/Closed]

froggy6703 - Mar 6, 2010 at 11:18 AM - Latest reply:  SWAJI
- Nov 19, 2014 at 07:02 AM
Hello,

I am trying to create a macro that will insert a set amount of blank rows into a existing spreadsheet after each entry. The amount of rows needed will be the same for each entry on the spreadsheet, but will need to be changed each time it is used.

Here is a sample...
Currently:
Title1
Title2
Title3
Title4

After Macro:
Title1


Title2


Title3


Title4

The amount of blank row can be anywhere from 20 - 40. I am hoping I can use some sort of Input box to get the number of rows needed so I don't need to modify to code each time.

Thank you and let me know if you can help.

Matt
See more 

20 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 6, 2010 at 09:47 PM
14
Thank you
A1 is having headings.
then try this macro
KEEP YOUR ORIGINAL FILE SAFELY SOMEWHERE
FIRST DO THE TESTING OF MACRO IN THE EXPERIMENTAL DATA YOU HAVE SENT
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
MsgBox r.Address
If r.Offset(1, 0) = "" Then Exit Do
Loop

End Sub

Thank you, venkat1926 14

Something to say? Add comment

CCM has helped 1687 users this month

This is really nice one (yes, just delete the line "MsgBox r.Address") this is correct.
Great! Thanks for sharing the macro.
Great, really helped me a lot! Thanks!
Just wondering, is it possible to set the range to a named cell? For instance, I want to insert a variable number of lines under each heading, but as the lines are inserted, the heading moves cells. Therefore, if I say range ("A30"), this may not end up being correct as lines may have been inserted, pushing this heading down.

I tried typinf in the cell name instead of A30, but that didn;t seem to work?
i dont know macro so how can I do the same in xl
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 7, 2010 at 07:30 PM
6
Thank you
sorry that is not the imput box but a message box. I have made that non operational copy this now

I have put an apostrophe in the beginning of the line "msgbox ......." to make it unoperable


Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")

Set r = Range("A2")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
'MsgBox r.Address(the apostrophe in the beginning of this line makes this line non operable)
If r.Offset(1, 0) = "" Then Exit Do
Loop

End Sub
Fairooz Mehedad - May 22, 2010 at 03:47 AM
Thanks man...was looking for something like this
just saved me loads of time.

Now just need something to copy the row immediately above the spaces into the now empty rows...
I've got no macro ability at all, but just dropped this into my excel 2003 and followed your instructions - it worked!
Thanks heaps
This works great. Thank you.
It is wonderful.
Thousands Thanks.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Feb 4, 2011 at 04:25 AM
3
Thank you
QUOTE
gah123 - Feb 4, 2011 8:00am GMT
just saved me loads of time.

Now just need something to copy the row immediately above the spaces into the now empty rows...
UNQUOTE

my previous message 10 month old. will it be possible for you to open a new thread
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 7, 2010 at 09:43 AM
0
Thank you
I did not understand. pop up box comes only once .once you enter for e.g then there will be 3 blank rows between the entries. you yourself said "I can use some sort of Input box " . please clarify your doubt with some examples.
Sorry if that didn't make sense, but I get the Input box, and that part works great. But then after each insert there is another pop-up that just has a cell name with an OK button. This then has to be pressed after each insert. I am wondering if there is a way to remove this pop-up box, but still keeping the input box.

Thank you,

Matt
bhunesh7 1 Posts Thursday January 31, 2013Registration date January 31, 2013 Last seen - Jan 31, 2013 at 05:28 AM
0
Thank you
9827755762