Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Accessing Workbook defined ranges 1

Status
Not open for further replies.

sabascal

IS-IT--Management
Aug 20, 2002
85
GB
Hi,

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

Thanks
 
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
 
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 for great xl and xl/VBA help
 
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...
Code:
For Each ws In Worksheets
  For Each n In ws.Names
    MsgBox n.Name & " refers to " & n.RefersTo
  Next
Next
:)



Skip,
Skip@TheOfficeExperts.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top