Deleting named ranges by looping through range collection
 

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

--
Message posted from http://www.ExcelForum.com

Deleting named ranges by looping through range collection
 

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

--

Vasant

"agarwaldvk " wrote in message
...
How could I delete all the named ranges from a workbook using VBA
looping procedures and not individually going through each one of them
from the front end worksheets.

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

Best regards and Thanks

Deepak


---
Message posted from http://www.ExcelForum.com/

Deleting named ranges by looping through range collection
 

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

--
Message posted from http://www.ExcelForum.com

Deleting named ranges by looping through range collection
 

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.

"agarwaldvk <" wrote:

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

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

Best regards and Thanks

Deepak

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson