Inserting Rows with Macro and expanding Named Range

Sub Test1()

Set C = Range("Rng1").Cells(Range("Rng1").Count, 1).Offset(1, 0)
Range("Rng1", C).name = "Rng1"

End Sub

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>
   ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
   Dim x As Long
   ActiveCell.Offset(-1, 0).Select
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault

    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
   

    Set C = Range("Cat1Totals").Cells(Range("Cat1Totals").Count, 1).Offset(1, 0)
    Range("Cat1Totals", C).Name = "Cat1Totals"

   
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Insert1")) Is Nothing Then
    Call InsertRowsAndFillFormulas 
End If

End Sub

ActiveCell.Offset(-1, 0).EntireRow.Insert

Sub Cat1RangeSelect()  'Dave Paterson, misc,
    Dim myRng As Range
    Set myRng = Worksheets("Sheet1").Range("Cat1SubTotal")
    With myRng
        .Parent.Select
        .Cells(.Cells.Count).Select
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
  If Target.Value = "Insert New Row" Then
    Application.EnableEvents = False
    Target.Offset(-1, 0).Activate
    Call InsertRowsAndFillFormulas(1)
    'Now update the named range
    'get the range name
    rangename = Target.Offset(0, 8).Value
    'get the range address
    rangeaddress = Range(rangename).Address
    'read the final row in the address - looking for the $
    i = Len(rangeaddress)
    Do Until Mid(rangeaddress, i, 1) = "$"
    i = i - 1
    Loop
    RowValue = Right(rangeaddress, Len(rangeaddress) - i)
    AddressValue = Left(rangeaddress, Len(rangeaddress) - Len(RowValue))
    NewNamedRangeValue = AddressValue & RowValue + 1
    ActiveWorkbook.Names.Item(rangename).Delete
    ActiveWorkbook.Names.Add Name:=rangename, RefersTo:="=" & NewNamedRangeValue
    Application.EnableEvents = True
  End If
End If
End Sub