Changed name range according to variable

[pablus]

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]List No.
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Pablo
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Pepe
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Luis
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Juan
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Manuel
[/TD]
[/TR]
</tbody>[/TABLE]

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