Named Ranges

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Is there any way to delete multiple named ranges? I have
inherited a file with 500+ named ranges that are invalid.
Holding Ctrl and Click or Shift and Click does not work in
the Named Range Dialogue Box.

Thanks
 
Try using Insert > Names > Define from the toolbar.

You can then select the range(s) you want to delete.

Hope this helps.

Andy
 
This macros will delete all named ranges.

Sub DeleteNamedRanges()
Dim oName As Name

For Each oName In Names
oName.Delete
Next
End Sub

HTH

-Dave
 
Try using Insert > Names > Define from the toolbar.

You can then select the range(s) you want to delete.

Hope this helps.
Click to expand...

Try rereading the OP (or simply reading it, since it appears you only skimmed it
the first time). The OP clearly knows how to bring up the Define Name dialog.
The problem is that the OP can't select batches of names to delete, but is
forced by the interface to delete them one at a time.
 
This macros will delete all named ranges.

Sub DeleteNamedRanges()
Dim oName As Name

For Each oName In Names
oName.Delete
Next
End Sub
Click to expand...
...

This works as claimed if the defined names are workbook-level names. On a
different tack, if some names should remain but a large batch should be deleted,
move the cell pointer to a blank range and run the menu command

Insert > Name > Paste, press the Paste List button

then delete the names from this list that should remain, select the range
containing all the remaining names that should be deleted, and run the following
macro.


Sub foo()
Dim c As Range, v As String

If Not TypeOf Selection Is Range Then Exit Sub

On Error Resume Next

For Each c In Selection

v = CStr(c.Value)
If v <> "" Then Names(v).Delete

If Err.Number <> 0 Then
Debug.Print "unable to delete name: '" & v & "' (cell " & c.Address(0, 0) & ")"
Err.Clear
End If

Next c

End Sub
 
If I'm doing almost anything with names, I'll use an addin written by by Jan
Karel Pieterse, Charles Williams and Matthew Henson. It's a really good way to
see what's happening with names.

You can find it at:
NameManager.Zip from http://www.bmsltd.co.uk/mvp

(It's much easier than using the builtin Insert|Name dialog.)

There's a multi button on the form that you'll like.
 
Back
Top