named ranges get larger

[thomas donino]

I have a sheet with a named range. every month i add a row of data via a
macro. How can I set the named range to also get one row larger?

 

[john]

have a look here:

http://www.contextures.com/xlNames01.html#Dynamic

 

[Rick Rothstein]

This should do what you want (just change the two occurences of "MyRange" to
the actual name of your range)...

With Range("MyRange")
ActiveWorkbook.Names("MyRange").RefersTo = Replace(.Name, .Address, _
.Resize(.Rows.Count + 1).Address)
End With

 

[Dave Peterson]

Or

with range("MyRange")
.resize(.rows.count+1).name = .Name.Name
end with

or

with range("MyRange")
.resize(.rows.count+1).name = "MyRange"
end with

If the name is local to the sheet (not a global name):

with worksheets("somesheetnamehere").range("MyRange")
.resize(.rows.count+1).name = "'" & .parent.name & "'!" & .Name.Name
end with

or
with worksheets("somesheetnamehere").range("MyRange")
.resize(.rows.count+1).name = "'" & .parent.name & "'!MyRange"
end with