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...