How to get a permanent Range address?

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi all,

I am trying to get the address of a range in a way that can be used
afterward even if the workbook or the sheet names change.

Now I am using the following:

range.get_Address(Type.Missing, Type.Missing,
Excel.XlReferenceStyle.xlA1, true, Type.Missing)

That will give me a string like this one: "[Book1]Sheet2!$A$1"

Retrieving the range object from that address will fail if, for
instance, I rename Sheet2 to Stocks.

Is there any way to do that?

Thanks.
 
Alex,

To do this you define a range variable and then set the variable equal to
the range. You can then reference the range even if the sheet or book name
changes:

Sub test()
Dim rngPerm As Range

Set rngPerm = Worksheets(2).Range("A1")
Worksheets(2).Name = "changed"
ThisWorkbook.SaveAs Filename:="temp"
MsgBox rngPerm.Address(external:=True)

End Sub

hth,

Doug
 
You can create a named range. The named range is a premanent range defined on
a sheet. You can access it via code similar to

Workbooks("MyBook.xls").names("MyName").referstorange.address
or
Workbooks("MyBook.xls").names("MyName").referstorange.parent
 
Back
Top