Array Help

[CadetFord2345]

I have an array that can vary in length. Is there a way to adjust this macro so that I don't have to use R1C1 to define the array? This is what my VBA looks like so far...

Windows("2022 Design Yield Report Ryan.xlsx").Activate
ActiveWorkbook.Names.Add Name:="myArray", RefersToR1C1:="=Summary!R4C2:R65C3"
Windows("Design Budget Forecast 2022 Ryan (TEST).xlsx").Activate
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row - 13
For i = 3 To FinalRow
If Cells(i, 3).Value = "GL Code" Then
Cells(i, 3).Select
ElseIf Cells(i, 3).Value = "" Then
Cells(i, 3).Select
Else
Cells(i, 3).Select
ActiveCell.Offset(, 17).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17], '2022 Design Yield Report Ryan.xlsx'!myArray, 2,)"
End If
Next i

If there's a way to make "R65C3" the bottom-right corner of my array every time, wherever it happens to be, that'd be amazing! As of now, I have to adjust R65 each time depending on where the final row is. I could potentially make it R1000 and that should be large enough for any array I use, but I'd love to use something that detects the bottom-right corner and can plug into the formula above, regardless if it's small or big. Appreciate any help, thanks!

 

[CadetFord2345]

I think I may have found a solution with...

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="myArray", RefersTo:=Range(Cells(4, 2), Cells(LastRow, 3))
VBA continued...

I'm always open to other ideas/solutions if they're out there.

Thanks again!