Deleting named ranges by looping through range collection

A

agarwaldvk

How could I delete all the named ranges from a workbook using VB
looping procedures and not individually going through each one of the
from the front end worksheets.

Do we have a range collection as we have collections for other object
like worksheets and workbooks etc?

Best regards and Thanks

Deepa
 
V

Vasant Nanavati

Do you want to:

a. delete the ranges;

b. delete the contents of the ranges; or

c. simply delete the range names?

If you want c., try something like:

Sub DeleteRangeNames()
Dim nm As Name, rng As Range
For Each nm In Names
On Error Resume Next
Set rng = Range(nm)
On Error GoTo 0
If Not rng Is Nothing Then nm.Delete
Set rng = Nothing
Next
End Sub
 
A

agarwaldvk

Thanks Vasant.

I think what you have provided is what I want. In essence, what
wanted was that these superfluous range names no longer appear in th
names list box at the top left corner of the workbook window.
certainly didn't want the range or the contents of the range deleted.

So, thanks again for correctly guessing what I wanted and the
providing the solution too.

Best regards


Deepa
 
D

Dave Peterson

I'd get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager.

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

If you use names a lot, you'll find it very, very useful.
 
Top