Changed name range according to variable

[pablus]

List No.	Name
1	Pablo
2	Pepe
3	Luis
4	Juan
5	Manuel

<tbody>
</tbody>

Hello everyone!

Let's say I have an small array as shown in the table and I want to create a named range for each column with all its elements. When I use the "Record Macro" tool it turn out this:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="list_no", RefersToR1C1:= _
"=Sheet1!R2C1:R6C1"
Range("B2:B21").Select
ActiveWorkbook.Names.Add Name:="names", RefersToR1C1:="=Sheet1!R2C2:R6C2"
End Sub

What if the size of the array changes? Can I use a variable X, something like RefersToR1C1:="=Sheet1!R2C2:RXCX" ?


Thank you all very much for your help. :D

 

[John_w]

Try this. Also, the code is more readable if you use A1 notation instead of R1C1.

Public Sub Create_Named_Ranges()
    
    Dim lastRow As Long
    
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ActiveWorkbook.Names.Add Name:="list_no", RefersTo:="='" & .Name & "'!$A$2:$A$" & lastRow
        ActiveWorkbook.Names.Add Name:="names", RefersTo:="='" & .Name & "'!$B$2:$B$" & lastRow
     End With
    
End Sub