Code to List All Named Ranges in a Worksheet

The following code will list down all the named ranges in an Excel Worksheet.

All you need to do is to select the Cell and run the code. Please note that the the code will return the name of the Named Range in the selected column below the active cell. Also, note that it is suggested that you choose a cell which has nothing below if else the data you have below the active cell in the same and the next column will be overwritten.

'===============
'Code Starts Here

Sub ListNamedRangesInWorkbookWhichReferstoActivesheet()
dim a

On Error Resume Next
For Each Name In ActiveWorkbook.Names

a = 0

a = WorksheetFunction.Find(ActiveSheet.Name, Name.RefersTo, 1)
If a <> 0 Then
i = i + 1
ActiveCell.Offset(i, 0).Value = Name.Name
ActiveCell.Offset(i, 1).Value = Name.RefersTo

End If

Next Name

End Sub

'===============
'Code Ends Here