Quickly changing or deleting Named Ranges

One thing that’s always irked me about working with Named Ranges is that while you may have selected the cells that a particular Named Range points at and can even see that name in the Name Box…

NameBox1

…you can’t do anything actually useful to it – such as changing where it points too or deleting it – without first firing up the NameManager, and then rummaging through the haystack for the particular name that you want to amend …

NameManager2

…and then clicking through another damned dialog box…

Edit Name

…or two…
NameManager Refers To

…or three…
Confirm

…purely to change where it points at. Deleting it is nearly as bad, too.

Wouldn’t it be cool if instead of all that rodent-work, you simply pushed some arbitrary keyboard short-cut of your choice, which then told Excel “Hey Excel, I want to resize or delete the Named Range that corresponds to my current selection. Can you do that for me? Can you? Huh?”

And wouldn’t it be cool if Excel then said…

Please select new range

Cool, indeed. File this baby in your Personal Macro Workbook, and trigger it with a keyboard short-cut of your choice:

Sub AmendSelectedName()

Dim nm As Name
Dim strRefersTo As String
Dim rngNew As Range
Dim rngExisting As Range

Set rngExisting = Selection
For Each nm In ActiveWorkbook.Names
    strRefersTo = nm.RefersTo
    If Replace(Replace(strRefersTo, "=", ""), "'", "") = ActiveSheet.Name & "!" & rngExisting.Address Then
        On Error Resume Next
        Set rngNew = Application.InputBox( _
            Title:="Please select new range", _
            Prompt:="Select new range for """ & nm.Name & """ or push Cancel to delete it.", _
            Default:=Selection.Address, _
            Type:=8)
        On Error GoTo 0
        If Not rngNew Is Nothing Then
            nm.RefersTo = "='" & ActiveSheet.Name & "'!" & rngNew.Address
            rngNew.Select
        Else: nm.Delete
        End If
    End If
Next

End Sub

9 Comments

  1. Matt says:

    AWESOME, thank you very much!

  2. John says:

    Awesome Jeff, we needed that one!

  3. GMF says:

    I think this will be really useful to adapt for also creating dynamic ranges. I hate typing in all the stuff like

    =Sources!$A$3:INDEX(Sources!$A$3:$A$100,COUNTA(Sources!$A$3:$A100))

    So much easier to call a macro that will identify the selection and automatically make it dynamic. (Although it’s quite possible that Excel already has a shortcut for this and I don’t know it.)

  4. Jeff Weir says:

    GMF: Good idea. I’m in the process of amending this routine so various options show up in the right-click menu, so I’ll add something that does as you suggest. I note that Dick already has such a routine in his PMW, so I’ll just pinch that!

  5. Ivan M. says:

    Though it is a good and useful macro, it seems that the code does not recognize names that refer to non-adjacent ranges.
    Sometimes it happen when a name is based on a set of non-contiguous cells. The macro adds Sheet name at the beginning, but it has to be before each sub-range

  6. Jeff Weir says:

    Hi Ivan. Yeah, I didn’t design it with non-adjacent ranges in mind…I was just looking for a way that I could more easily identify and change Named Ranges tied to specific blocks on the worksheet, and cut down on clicking and scrolling in the NameManager in order to find the relevant name given that worksheet block. I’ll have to have a think about non-adjacent ranges…but that’s kind of outside the scope of what I was trying to do, and probably best handled with the native NameManager in any case.

    Cheers for the comment.

  7. […] that lets you create lots of Dynamic Named Ranges from your current selection, in response to a question from GMF. (Now that is a strange […]

  8. […] Weir shares his macro for quickly changing or deleting Named Ranges. But if you’re not using a macro, you can edit the Refers To formula at the bottom of the Name […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: