How to loop through all named ranges in a sheet?

[Ceeyee]

I want to msgbox each of the names of all the ranges that I have given a name to in a sheet.

How can I do that?

If I do it by checking each cell in the worksheet using .name.name, then when it loop to an un-named cell it gives an object error.

Thanks.

 

[jbeaucaire]

Maybe:

Sub Ranges()
Dim r As Name

For Each r In ActiveWorkbook.Names
    MsgBox r.Name & " - " & Range(r).Address
Next r

End Sub

 

[Ceeyee]

Thank you very much.

 

[brettdj]

Thank you very much.

Were you looking to find names only in a particular sheet or as indicated above is this solved?

Cheers

Dave

 

[Ceeyee]

Only in a particular sheet...

 

[brettdj]

This code reports only name that have a range on the first of the active workbook

To look at the second sheet use
Set ws = ActiveWorkbook.Sheets(2)

A sheet called test
Set ws = ActiveWorkbook.Sheets("test")

etc

hth

Dave

Sub Ranges()
    Dim r As Name
    Dim rng1 As Range
    Dim strOut As String
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets(1)
    'Not all Names are ranges
    For Each r In ActiveWorkbook.Names
        On Error Resume Next
        If Range(r).Parent.Name = ws.Name Then
            If Err.Number = 0 Then strOut = strOut & r.Name & vbNewLine
        End If
        On Error GoTo 0
    Next r
    If Len(strOut) > 0 Then
        MsgBox "These ranges found in: " & ws.Name & vbNewLine & strOut
    Else
        MsgBox "No names ranges on " & ws.Name
    End If
End Sub

 

[Ceeyee]

Waah, thank you!

 

[brettdj]

No problem, glad to have helped