resize named range

[MetLife]

Hi,

I have a named range that is a 25 x 100 table. I am importing data from another csv into this table and the rows will change, so I want to be able to automatically update the named range from

"A1:BB100"

to

"A1:BB250"

if the number of rows increases, is there an easy way to do this in VBA?

Thanks

 

[JoeMo]

Hi,

I have a named range that is a 25 x 100 table. I am importing data from another csv into this table and the rows will change, so I want to be able to automatically update the named range from

"A1:BB100"

to

"A1:BB250"

if the number of rows increases, is there an easy way to do this in VBA?

Thanks

You can adapt this to do what you want.

Sub ResizeNamedRange()
Dim R As Range
Set R = Range("myRng")  'change name of named range to suit - assumes name is scoped to workbook level
R.Name.RefersTo = Range("A1:BB250")  'set the new range
MsgBox Range("myRng").Address 'verify new range is what is wanted
End Sub

 

[Joe4]

If you want to dynamically find the end of the new range (and let's say we can use column A to do that), then for a named range of "MyRange" on Sheet1, this code would work:

    Dim lastRow As Long
    Dim nmRng As Name
    
'   Capture named range
    Set nmRng = ActiveWorkbook.Names.Item("MyRange")
    
'   Find last row of data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Reset range reference of named range
    nmRng.RefersTo = "=Sheet1!$A$1:$B$" & lastRow