 <p>try this</p> <pre><code>Option Explicit Sub test() Dim j As Long Dim rng As Range, cell As Range Dim i As Long j = Application.InputBox("No. of rows to be inserted?", Type:=1) With Worksheets("REFS") '&lt;-_| change "REFS" to your actual worksheet name i = 1 With .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)) Do While i &lt;= .Rows.Count With .Cells(i).EntireRow .Copy .Offset(1).Resize(j).Insert Shift:=xlDown Application.CutCopyMode = False End With i = i + j + 1 Loop End With End With End Sub </code></pre> <p>I'm using <a href="https://msdn.microsoft.com/en-us/library/office/ff839468(v=office.15).aspx" rel="nofollow">Application.InputBox()</a> method instead of <a href="https://msdn.microsoft.com/en-us/library/office/aa195768(v=office.11).aspx" rel="nofollow">VBA InputBox()</a> one because the former lets you force the user input data type (in the example Type:=1 forces <em>numeric</em> input)</p>