A few words of thanks would be greatly appreciated.

Excel - A macro to insert blank rows

Latest update on October 22, 2014 at 06:51 AM by Jean-François Pillou.

Issue
Solution

Issue

You're trying to create a macro that will insert a set of blank rows into an existing spreadsheet after each entry. The amount of needed rows will be the same for each entry of 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 go from 20 - 40.

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.