How to Create a Dynamic Named Range - Replicating A1:INDEX(range, COUNT()) in VBA?

I have a sheet that pulls in data from an API across rows. So 1 formula will cover, say 6000 columns in row 1, the same as row 2. Each of these can vary in length. Therefore I need to create Dynamic Named Ranges.

I have figured out how to loop using a For...Next loop down a row (with the help of SO), but this simply produces the generic cell range instead of the formula's optionality.

I need this now because I am dealing with over 300 ranges.

Sub SeriesScripts()

    Dim lastcol As Long, i As Long, ws As Worksheet, rng As Range

    Set ws = ThisWorkbook.Sheets("Master Sheet")
    
    For i = 1 To 3
        lastcol = ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column
        Set rng = ws.Cells(i, 1).Resize(1, lastcol)
        ThisWorkbook.Names.Add Name:="Series_" & i, RefersTo:=rng
    Next i

End Sub

This is fine for adding ranges that have a range I didn't know about, but the SERIES() object is still fixed as a set range.

I am not familiar with R1C1 syntax. Maybe this is the solution?

Solution

My formula proposals in two variants. The first – when all cells are filled, the second – when some cells may be empty.

Sub SeriesScriptsA()
    Dim i As Long, ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Master Sheet")
    For i = 1 To 3
        ThisWorkbook.Names.Add Name:="Series_" & i, RefersToR1C1:= _
            "=" & ws.Name & "!R" & i & "C1:INDEX(" & ws.Name & "!R" _
            & i & ",COUNTA(" & ws.Name & "!R" & i & "))"
    
        ThisWorkbook.Names.Add Name:="SeriesA_" & i, RefersToR1C1:= _
            "=" & ws.Name & "!R" & i & "C1:INDEX(" & ws.Name & "!R" _
            & i & ",MATCH(2,1/(" & ws.Name & "!R" & i & "<>"""")))"
    Next i
End Sub