EXCEL: Create/delete rows with autofilled data/functions matching to a number in a cell

My problem is that I have a small fo开发者_开发知识库rmula which have a minimum of 2 rows and should be able to be expanded to a given number from another cell. E.g. 2 rows minimum - then the cell numbering the amount of rows is set to 10. Then there will be added another 8 rows between row 1 & 2. With autofilled data that is similar to when you drag it down using the autofill option. Again when you set down the number from 10 to 4 then 6 of the rows should be deleted.

Hope that you can see the idea of what I am thinking and again I'm very sorry if this is unclear or not fulfilling all requirements to a question.

1. Define three named cells -- say, FirstCell, LastCell and HowMany. Make sure LastCell is right below FirstCell.

2. Create a Worksheet_Change handler. If the Target range is the HowMany cell, then add or remove cells between FirstCell and LastCell:

   If Target.Address = Me.Range("HowMany").Address Then
     If Not IsEmpty(Target.Value) Then
       If IsNumeric(Target.Value) Then
         If Target.Value >= 2 Then
   
           Dim i As Long
           Dim rows_before As Long
   
           rows_before = Me.Range("LastCell").Row - Me.Range("FirstCell").Row + 1
   
           If rows_before < Target.Value Then
             For i = 1 To Target.Value - rows_before
               Me.Range("LastCell").Insert xlShiftDown
             Next
           ElseIf rows_before > Target.Value Then
             Me.Range(Me.Range("LastCell").Offset(-1, 0), Me.Range("LastCell").Offset(-(rows_before - Target.Value), 0)).Delete xlShiftUp
           End If
   
           Me.Range(Me.Range("FirstCell"), Me.Range("LastCell")).FillDown
         End If
       End If
     End If
   End If