How to insert blank rows using macros in Excel

You're trying to create a macro that will insert a set of blank rows into an existing spreadsheet after each entry. The number of needed rows will be the same for each spreadsheet entry but must be changed each time you modify it.

To insert blank rows using a macro in Excel:

Here is an example with A1 having headings. Keep your original file safely somewhere and try this macro:

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