named ranges get larger

T

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?
 
R

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
 
D

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
 
Top