Changing the range of a named range (special)

[luzikedy]

Hi All,

I need for my project the following:

My range has to have always 1500 rows down from from a specific cell that is described by a value in cell B1.

I have a named range called Myrange with this area covered $A$1:$A$1500 and in a cell B1 I have value 1000 and I need a code that changes the range to $A$1000:$A$2500.

Thanks for help!

 

[MARK858]

Try the code below

Sub renameit()
Range(Cells(Range("B1").Value, "A"), Cells(Range("B1").Value + 1500, "A")).Name = "Myrange"
End Sub

Edit: was being stupid as the OP already stated the name

 

[kalak]

Hi All,

I need for my project the following:

My range has to have always 1500 rows down from from a specific cell that is described by a value in cell B1.

I have a named range called Myrange with this area covered $A$1:$A$1500 and in a cell B1 I have value 1000 and I need a code that changes the range to $A$1000:$A$2500.

Thanks for help!

Do you mean?

Range("myrange").Offset([b1] - 1).Name = "myrange"

[myrange].Select  'this line is just to check

 

[luzikedy]

Works perfect! Thank you!