Named Ranges in Excel

Topic

New Reply

Does anybody know how to:
1. Change what cells a range name refers to?
2. Examine what cells a range name refers to?
3. Loop through all range names?

Alternatively, is it possible to access named ranges of one workbook from a worksheet in another workbook?

Reply | Quote

Replies

Well, it just so happens I can answer some of your question, as I’m doing a project on this very thing right now.
1. Change what cells a range name refers to?
try:
ActiveWorkbook.Names.Add Name:=”test”, RefersToR1C1:=”=Sheet1!R6C4:R14C5″
where test refers to the existing range name. You can use alphanumeric row/column referencing.

2. Examine what cells a range name refers to?
By this I assume you mean, navigate to the range itself?
Try:
Application.Goto Reference:=”test”

3. Loop through all range names?
I’d be fascinated to hear a suggestion on that one myself.

Cheers
Catharine

Reply | Quote

In answer to questions 2 & 3:

Dim xlnname As Excel.Name
For Each xlnname In Excel.Names
Debug.Print xlnname.Name; ” “; xlnname.RefersTo
Next

Reply | Quote

Cool!

Reply | Quote

Yeah, it took a little bit of digging to find that when I needed it. I found it eventually by recording the action of creating a named range.

BTW, your pic would be improved by setting a transparent background. You can do that through many of the paint programs like PaintShop Pro.

Reply | Quote

And here I’ve been spending an unreasonable amount of time trying to get a groovy signature like you!

Reply | Quote

Thanks to both of you.

Reply | Quote