Excel - A macro to insert blank rows

Issue

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 using 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.

Solution

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  

Thanks to venkat1926 for this tip.

See also

Knowledge communities.

• Forum
• Excel forum
• Programming forum
• Office software forum

This document entitled « Excel - A macro to insert blank rows » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.