Macros, Adding rows based on user input (# and location)

[xfg85]

I am trying to write up a macro that will allow the user to select how many rows they want to add and determine where they want to add these rows. I have been able to add rows after line 40 but can't figure out how to modify it so that they can enter a specific location.

Sub AddRow()
Dim j As Long, r As Range
j = InputBox("How many rows do you want to add?")
'where the adding rows start but want to customize it so they can pick anywhere
Set r = Range("A40")
Do
'add the row
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
'add in the formatting
Range("E40:G40").Select
Selection.Copy
Set s = Range("E40")
Range(s.Offset(1, 0), s.Offset(j, 0)).Select
ActiveSheet.Paste
'make sure new rows do not have any data to them
Range(s.Offset(1, 0), s.Offset(j, 0)).ClearContents
Range(s.Offset(1, 1), s.Offset(j, 0)).ClearContents
'reset r counter - 1 and loop till done
Set r = Cells(r.Row + j + 1, 1)
If r.Offset(1, 0) = "" Then Exit Do
Loop
Range("A41").Select
End Sub

 

[mrmmickle1]

Try something like this:

[COLOR=#0000ff]Sub [/COLOR]AddRow()

   [COLOR=#0000ff] Dim [/COLOR]AddRows  [COLOR=#0000ff]As Long[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] Where    [COLOR=#0000ff]As Long[/COLOR]
    
    AddRows = Application.InputBox("How many rows do you want to add?", Type:=1)
    Where = Application.InputBox("What row do you want to add these below?", Type:=1)
    Cells(Where + 1, "A").Resize(AddRows, 1).EntireRow.Insert

[COLOR=#0000ff]End Sub[/COLOR]