Accessing Workbook defined ranges 1

[sabascal]

Hi,

Given a Workbook("wrkb1&quot how can I access all the ranges that has been defined in this workbook. I actually need to loop through them.

Thanks

 

[combo]

You can:
[tt]Dim n As Name
For Each n In ThisWorkbook.Names
Range(n.Name).Select
MsgBox "selected: " & n.RefersToRange.Address & vbLf & "as " & n.Name
Next n[/tt]

combo

 

[Chattin]

Or try this - lists the names on a spreadsheet

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.Names
Cells(Row, 1) = n.Name
Cells(Row, 2) = " " & n.RefersTo
Row = Row + 1
Next n
End Sub

HTH

Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help

 

[SkipVought]

Hi,

Actually you guys are almost there.

There are WORKBOOK Range Names and can also be WORKSHEET Range Names.
So in ADDITION to the above...

For Each ws In Worksheets
  For Each n In ws.Names
    MsgBox n.Name & " refers to " & n.RefersTo
  Next
Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com