vb code change help

[redspanna]

this code below I have found on a previous post by another member but it doesn't quite do what I want

Sub InsertRows()

    Dim lngRows As Long, lngNextRow As Long

    On Error GoTo Finish

    lngRows = CLng(InputBox("How many rows do you wish to insert?"))
    lngNextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
    
    Rows(3).Copy Rows(lngNextRow & ":" & lngNextRow + lngRows - 1)
       
Finish:
    If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

instead of inserting the number of rows given by the msgbox input at the first empty row, I want it inserted at the firat empty row ofsett by -7 rows.
so if last empty row in column B is row 107 I want xx rows inserted at row 100

any help appreciated

 

[Peter_SSs]

Is this what you want?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> InsertRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> Rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Do</SPAN><br>        Rws = Application.InputBox( _<br>            Title:="How many rows do you wish to insert?", _<br>            Prompt:="Enter a whole number > 0", _<br>            Type:=1)<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Rws < 0<br>    <SPAN style="color:#00007F">If</SPAN> Rws > 0 <SPAN style="color:#00007F">Then</SPAN><br>        LastRw = Range("B" & Rows.Count).End(xlUp).Row<br>        <SPAN style="color:#00007F">If</SPAN> LastRw < 7 <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Unable to add rows in correct location"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Rows(LastRw - 6).Resize(Rws).Insert<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>