named ranges get larger

  • Thread starter Thread starter thomas donino
  • Start date Start date
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?
 
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
 
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
 
Back
Top