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 thoughts on “Quickly changing or deleting Named Ranges

  1. 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.)

  2. 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!

  3. 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

  4. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.