VBA Listnames of Range Excel

VBA Listnames of range object will list the names in the workbook. It will list all the available named ranges and their corresponding definitions. If no names are defined in the workbook, it results nothing. This will be very useful if you have lot of named ranges in your workbook and see the all definitions at one glance. Particularly when you are moving the worksheets or deleting the worksheets, some of the named definitions will be corrupted(lost the refereces), In this case you can list all the available names in the workbook and see if they are missing any references.

VBA ListNames of Range – Syntax

Here is the syntax to find listnames in Excel range object. It will list all the available named ranges and their corresponding definitions.

Range(“YourRange”).ListNames

VBA ListNames of Range – Example

Below is the Excel VBA Macro or code to list all the available named ranges in the range “A1” and their corresponding definitions in the range “B1”

Sub Range_ListNaames()
    Range("A1").ListNames
End Sub

VBA ListNames of Range – Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

1. Open an Excel Workbook from your start menu or type Excel in your run command
2. Add or define few names in your workbook to test the ListNames macro.
3. Press Alt+F11 to Open VBA Editor or you can go to Developer Tab from Excel Ribbon and click on the Visual Basic Command to launch the VBA Editor
4. Insert a Module from Insert Menu of VBA
5. Copy the above code (for copying a range using VBA) and Paste in the code window(VBA Editor)
6. Save the file as Macro Enabled Workbook (i.e; .xlsm file format)
7. Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line.

Now, it will list all the available named ranges in the range “A1” and their corresponding definitions in the range “B1”. If no names are defined in the workbook, it results nothing. It is always a better practice to list the names in the workbook and check their definitions.