How to add message box to add a specific number of cells or rows

[zhead]

I have a macro that works great as is but I am trying to add a Message box that will ask for the number of rows to be added between each row of data. the way it is right now it will add 17 rows between each row of data. Can someone please help?

Sub RunMe()
Dim x As Integer

x = 2

Do
    Rows(x).Resize(16).Insert
    x = x + 17
Loop Until IsEmpty(Cells(x, "A"))
 
End Sub

 

[jasonb75]

This will do it, you can change the prompt as desired.

Sub RunMe()
Dim x As Long, y As Long
x = 2
y = InputBox("Insert how many rows?")
Do
    Rows(x).Resize(y).Insert
    x = x + y + 1
Loop Until IsEmpty(Cells(x, "A"))
 
End Sub

 

[Kenneth Hobson]

Maybe:

Sub RunMe()
  Dim xx As Integer, x As Long 'Use Long for rows, columns as integer is ok.
  x = 2
  xx = Application.InputBox("Enter number of rows to add.", "Row Count to Add", Type:=1)
  Do
    Rows(x).Resize(xx-1).Insert
    x = x + xx
  Loop Until IsEmpty(Cells(x, "A"))
End Sub

 

[zhead]

jasonb75 Thanks that is exactly what i been trying to do. One more question. Is there a way to start with row 2 as my document has headers.

 

[zhead]

Kenneth yours works just as well. Thanks. I ask you the same question. Is there a way to start with row 2 as my document has headers?

 

[jasonb75]

Changing to x = 3 should do the trick.

There is one slight difference between my code and Kenneth's, I followed the description in your first post and inserted the specified number of rows, while it appears that Kenneth has followed your original code and included the data row in the block so that 1 less row is inserted.

 

[zhead]

Thanks again to both of you.