How to insert blank rows using macros in Excel

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
any more questions about excel? check out our forum!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11

Excel