For those times when I have many range names to create...perhaps in
several existing workbooks...I use this VBA code in my Personal.XLS workbook
(but you could put it in any workbook):

Copy/Paste the below code into a Genaral Module.
Sub CreateRangesFromList()
'This procedure creates new range names in the active workbook
'using a 2-column range of cells that contains:
'Col 1: the text of the range name to be created
'Col 2: text of the RefersTo definition (in proper format: ="somesheet!someref")
   Dim intRCount As Integer
   Dim intRRef As Integer
   
   Dim strSelAddr As String
   Dim rngBase As Range
   
   On Error GoTo FailTrap
   With selection
      If .Columns.Count <> 2 Then
         MsgBox Title:="Invalid Base Range", _
               Prompt:="Range does not contain exactly 2 columns", _
               Buttons:=vbCritical + vbOKOnly
         Exit Sub
      End If
      intRCount = .Rows.Count
      Set rngBase = .Cells(1, 1)
   End With
   
   With rngBase
         
      For intRRef = 1 To intRCount

         On Error Resume Next
         ActiveWorkbook.Names.Add _
            Name:=.Cells(intRRef, 1).Formula, _
            RefersTo:=.Cells(intRRef, 2).Formula
         If Err.Number <> 0 Then
            MsgBox Title:="Problem with this item:", _
                  Prompt:=.Offset(RowOffset:=intRRef).Address
         End If
      Next intRRef
   End With
   Set rngBase = Nothing
Exit Sub

FailTrap:
   Set rngBase = Nothing
   MsgBox Title:="Problems Encountered", _
         Prompt:="Check list for invalid Range Name Text and/or Refers_To values.", _
         Buttons:=vbCritical + vbOKOnly
         
End Sub

Here's an example of how I'd use it....
First, I create a 2-column list, comprising of:
-Name to be created
-Refers to expression (formatted as text)

Example:
G1: MyDynRangeName
H1: '=OFFSET($C$3,,,COUNTA($C:$C),3)

Select G1:H1
[ALT]+[F8]...a shortcut for <tools><macro><macros>
Select: CreateRangesFromList
Click [Run]

The code will create or rebuild the range names in the list.

In the above example, a dynamic range name is created that
refers to C3:D3 and down for the number of non-blank cells in Col_C.

Is that something you can work with?