excel vba List of all named ranges
' List of all named ranges ("my_range" - > [Sheet1!$A$3:$B$5])
' Add some named range to test
Sub AllNamedRanges()
Dim nRangeName As Name
Dim rNamedRange As Range
Dim sNameDetails As String
Dim iIndiceWS As Long
Dim sNamedList As String
With ThisWorkbook
For iIndiceWS = 1 To .Worksheets.Count
If .Names.Count > 0 Then
For Each nRangeName In .Names
Set rNamedRange = nRangeName.RefersToRange
If Not rNamedRange Is Nothing Then
If .Worksheets(iIndiceWS).Name _
= rNamedRange.Worksheet.Name Then
sNameDetails = nRangeName.Name _
& " [" & rNamedRange.Worksheet.Name _
& "!" & rNamedRange.Address & "]"
' sNameDetails can be stored in a Collection too
sNamedList = sNamedList & sNameDetails & vbCrLf
End If
End If
Next nRangeName
End If
Next iIndiceWS
End With
Debug.Print sTextTemp
End Sub