Define Name by vba

[Forestq]

Hi,

I'm using "Name Manager" in Formulas Tab to define LIST. Exaclty the same I want to do using VBA.

I have:

=OFFSET('Country'!$E$9,0,0,COUNTA('Country lvl'!$E$9:$E$20),1)

Into VB:

rows_x = ws.Cells(Rows.Count, "A").End(xlUp).Row

wb_1.Names.Add Name:="Country_list", RefersToR1C1:= _
        "=OFFSET('Country lvl'!R9C5,0,0,COUNTA('Country lvl'!R9C5:R10C5),1)"

But I dont know how to change the range to: !$E$9:$E$rows_x

 

[Andrew Poulsom]

Like this?

wb_1.Names.Add Name:="Country_list", RefersToR1C1:= _
        "=OFFSET('Country lvl'!R9C5,0,0,COUNTA('Country lvl'!R9C5:R" & rows_x & "C5),1)"

 

[Forestq]

yes,

but how I now that is for E or G etc column?

 

[Andrew Poulsom]

E is column 5 (C5).

 

[Forestq]

thanks Andrew!