Name range is one the useful feature to provide name to a cell or group of cell in excel worksheet. Many a times, user needs multiple named ranges in excel workbook. Then it becomes very difficult to list down all the names ranges created in each worksheet. We can automate this task and list down all named ranges from excel workbook.










List of named ranges can be get easily by the excel vba macro. Which will be very helpful to use and modify listed named ranges in the workbook.

Here is the code which will list all named ranges from the workbook:

Sub List_All_Named_Ranges()

Dim nRng As Name
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

    Sheet1.Cells(1, 1).Value = "Name"
    Sheet1.Cells(1, 2).Value = "Range"
    
    i = 2

' Loop through all Name ranges in workbook    
    For Each nRng In Names
        Sheet1.Cells(i, 1).Value = nRng.Name
        Sheet1.Cells(i, 2).Value = nRng
        i = i + 1
    Next nRng

    MsgBox "All named ranges listed successfully", vbInformation

End Sub

Share this post if you think this is really useful...