Insert Multi Rows Between Existing Data Solved/Closed

Question

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

venkat1926 · Accepted Answer

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

venkat1926 · Answer

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.

venkat1926 · Answer

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

venkat1926 · Answer

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

bhunesh7 · Answer

9827755762

Insert Multi Rows Between Existing Data

5 responses

Similar discussions

Subscribe To Our Newsletter!