Ask a question »

Excel - A macro to insert blank rows

May 2015


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.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-a-macro-to-insert-blank-rows.pdf

See also

In the same category

Published by aakai1056. - Latest update by Jeff
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.