excel vba List of all named ranges code example

Example: 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