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?

have a look he

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


"thomas donino" wrote:

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?

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

--
Rick (MVP - Excel)


"thomas donino" wrote in message
...
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?

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


Rick Rothstein wrote:

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

--
Rick (MVP - Excel)

"thomas donino" wrote in message
...
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?

--

Dave Peterson