What it does
Uses picker prompt to provide several options for working with named ranges, including defining them, copying them, searching through them.  See the full list of options below in the Instructions section.

When to use it
When working with named ranges, this offers several options that are scattered throughout various Excel menus and combine actions to make them easier to work with.

Why to use it
It’s easier and quicker than using the mouse, combining several clicks into one step, providing shortcuts to tougher-to-find menu options, and providing options that extend the native capabilities of Excel.

Default shortcut
None

Other Details

• Category: Formulas / Named Ranges
• Difficulty: 3/5
• Usage/frequency: 3/5
• Automation factor: 3/5 (estimated 10 seconds saved each time used)
• Type: Shortcut
• Date added: 9/12/2019
• Tags: named ranges, data validation, static, dynamic, picker

Related Macros
[A] Add Static Named Range
[D] Add Dynamic Named Range
[C] Copy Named Range
[S] Search Named Ranges
[V] Add Data Validation From Named Range
[B] Bulk Edit Named Ranges

Note: most of the related filter macros above will be covered in the instructions below.  The picker option code is displayed in the [brackets].

Other Articles
None

None

Prerequisites
Options A and D: Select the range for which you’d like to assign a named range.
Option C: Select cell(s) within a range name you’d like to copy.
Option V: Select the range for which you’d like to apply data validation.
Options B: Identify the ranges for which you’d like to apply named ranges.

Instructions
With cells selected per the prerequisites above, run the Named Range Picker macro.  It will present several helpful actions to run for working with named ranges.  For the action you run most often, you can set your default so you don’t have to enter it (just launch the picker and press Enter).  Note that the default is highlighted when the picker is launched, so to change the action code, there is no need to press the backspace first.  Further instructions for the individual action codes are described below.

• [A] This will apply a static named range to the selected cell(s).  If the range contains multiple cells, it will not expand if data is added below or to the right of the named range.
• [D] This will apply a dynamic named range to the selected cell(s).  You can elect to have the dynamic named range expand below (vertically) or to the right (horizontally) of the named range as data is added below or to the right (expanding rows/vertically is most common).
• [C] If the selected cell(s) are within a named range, the named range is copied to the clipboard.  If the selected cell(s) are within multiple named ranges, the first named range found will be copied to the clipboard.
• [S] This will display the named range search box.  Type one or more characters to search through all named ranges in the active workbook and jump to them.
• [V] This will add data validation to the selected cell(s) and reference the range name supplied (default is any text copied to the clipboard).
• [B] This will run the Bulk Edit Named Ranges macro, which uses a settings sheet to make named range updates in bulk.
• [Q] This will display the native Excel named ranges dialog box.

Like most of the picker macros, you can set the default option to apply so you don’t even have to enter the letter/number – just the Enter key.  Just enter your preferred default letter(s), then click the default checkbox, and those letter(s) becomes the new default.