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.
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
