Name Range

[Hashiru]

Hi everyone,

I am trying to name a range from VBA with ranges that changes. Here is my code

RowNum = iReport.Cells(Rows.Count, 1).End(xlup).Row

iSht.Names.Add Name:="NamedRange", RefersToR1C1:="=iSolReport!R2C2:R2229C22"

I want to be able to change R2229 to RowNum

Thanks

 

[taigovinda]

Hi,

iSht.Names.Add Name:="NamedRange", RefersToR1C1:="=iSolReport!R2C2:R" & RowNum & "C22"

...assuming you actually want the named range to be hard-coded. You can also use formulas to make your named range size automatically update (search for 'dynamic named range' where you use index() or offset()) or you can convert the range to a table and then just reference the table, whose dimensions will automatically change.

Tai

 

[Hashiru]

Hi,

iSht.Names.Add Name:="NamedRange", RefersToR1C1:="=iSolReport!R2C2:R" & RowNum & "C22"

...assuming you actually want the named range to be hard-coded. You can also use formulas to make your named range size automatically update (search for 'dynamic named range' where you use index() or offset()) or you can convert the range to a table and then just reference the table, whose dimensions will automatically change.

Tai

Thanks for the wonderful piece of references.