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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBAListNames Method Excel Range Object

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.

LIMITED TIME OFFER

One Comment

  1. John Christensen April 25, 2016 at 7:47 AM - Reply

    I’m confused – How is “B1” specified in the code example above? If I had this in the first row of a spreadsheet:
    pi 3.1415
    Then when I went to assign named ranges, the named range would be B1, not A1, right? The naming method (Alt-ShiftF4, if I remember) just uses the A1 cell to name the named range?
    Isn’t Range(“A1”).ListNames just going to only look in A1, not A1 and B1?
    thanks,
    John

Leave A Comment


Related pages


vba run applicationmsgbox in accessinteractive gantt chart excelconsolidate excel columnsexcel macro activate workbookexcel color index valuessave workbook in excelinterview questions for programmers and answersvba deleteworkbook passwordvbscript msgbox yes nousing macros in excel 2007unhide sheets in excel 2007excel macro iconhow to change macros in excelcustomer service dashboard excelvba set workbook nameif statement in excel 2007excel vba and macrosexcel advanced filter criteria range examplesresource capacity planning excel templateinsert multiple rows excel 2010remove hyperlinks in excelsas predictive modeling tutorialvba beginners tutorialhow to format rows in excelvba databaseadvanced pivot table excel 2007excel macro tutorial for beginnersmicrosoft estimate templateexcel 2007 vba macro programming pdfexcel show duplicate valuescells.find vbashow hidden sheets in excelwhat is descending order in excelwindows excel shortcutsgroupby and orderby in sqlinserting lines in excelhow to create a command button in excelsas beginnersexamples of formulas in excelvba dir functionunhide columns in excelexcel vba formusing vba functions in excelhow to do pivot tables in excel 2007worksheet on coloursvba excel sheetvba excel commandscut and paste methodms access vba export to excelinteger in vbaexcel vba selectexcel vba case selectvba runsqlexcel combine worksheets into one worksheethow to delete repeats in excelhow do you delete cells in excelhow to send email using vbaexcel hyperlink to macroexcel formula definitionsunprotect excel workbook 2007excel sheet hiddenvba excel basicsproject task tracker excelautomation interview questions and answerscountif in excel exampleremove duplicate entries excelmsgbox yes no cancelworking with pivot tables in excel 2007excel advanced vbacolour index exceluse excel to find duplicateshow do i filter out duplicates in excelproject management timeline template excelhow to use hlookup in excel 2007 with example