How do I clear or reset all range names once I am finished a procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

The Workbook.Names collection holds all the named ranges used in your workbook.
This clears the cells (contents and formats) and deletes the named range
from the list of names:
Dim N as Name
For Each N in ThisWorkbook.Names
With N.RefersToRange
.ClearContents
.ClearFormats
.Delete
End With
Next N
--
- K Dales


"newnhamm via OfficeKB.com" wrote:

How do I clear or reset all range names once I am finished a procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Thanx, I'll try it now!

Mike

K Dales wrote:
The Workbook.Names collection holds all the named ranges used in your workbook.
This clears the cells (contents and formats) and deletes the named range
from the list of names:
Dim N as Name
For Each N in ThisWorkbook.Names
With N.RefersToRange
.ClearContents
.ClearFormats
.Delete
End With
Next N
How do I clear or reset all range names once I am finished a procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

I have tried what you suggested but I don't want to clear the cells, just
delete the names so that I can set the ranges again. Would I omit :

.ClearContents
.ClearFormats

??

Mike

newnhamm wrote:
Thanx, I'll try it now!

Mike

The Workbook.Names collection holds all the named ranges used in your workbook.
This clears the cells (contents and formats) and deletes the named range
[quoted text clipped - 8 lines]
Next N
How do I clear or reset all range names once I am finished a procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

That left names with bad references in the workbook for me. Perhaps K Dales
meant this:

sub abc()
Dim N As Name
For Each N In ThisWorkbook.Names
With N.RefersToRange
.ClearContents
.ClearFormats
End With
Next N
End Sub

this would assume that you don't have names that contain formulas rather
than refer to ranges. It also assumes you want to retain the names in the
workbook.

--
Regards,
Tom Ogilvy




"K Dales" wrote in message
...
The Workbook.Names collection holds all the named ranges used in your
workbook.
This clears the cells (contents and formats) and deletes the named range
from the list of names:
Dim N as Name
For Each N in ThisWorkbook.Names
With N.RefersToRange
.ClearContents
.ClearFormats
.Delete
End With
Next N
--
- K Dales


"newnhamm via OfficeKB.com" wrote:

How do I clear or reset all range names once I am finished a procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

sub abc()
Dim N As Name
For Each N In ThisWorkbook.Names
n.Delete
Next
End Sub

--
Regards,
Tom Ogilvy

"newnhamm via OfficeKB.com" <u16692@uwe wrote in message
news:592aff0a93034@uwe...
I have tried what you suggested but I don't want to clear the cells, just
delete the names so that I can set the ranges again. Would I omit :

.ClearContents
.ClearFormats

??

Mike

newnhamm wrote:
Thanx, I'll try it now!

Mike

The Workbook.Names collection holds all the named ranges used in your
workbook.
This clears the cells (contents and formats) and deletes the named range
[quoted text clipped - 8 lines]
Next N
How do I clear or reset all range names once I am finished a
procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Thanks for fixing my mistake, Tom! It was carelessness on my part; the
..Delete should not have been inside the With N.RefersToRange, since I meant
to delete the Name object and not the range it referred to.
--
- K Dales


"Tom Ogilvy" wrote:

sub abc()
Dim N As Name
For Each N In ThisWorkbook.Names
n.Delete
Next
End Sub

--
Regards,
Tom Ogilvy

"newnhamm via OfficeKB.com" <u16692@uwe wrote in message
news:592aff0a93034@uwe...
I have tried what you suggested but I don't want to clear the cells, just
delete the names so that I can set the ranges again. Would I omit :

.ClearContents
.ClearFormats

??

Mike

newnhamm wrote:
Thanx, I'll try it now!

Mike

The Workbook.Names collection holds all the named ranges used in your
workbook.
This clears the cells (contents and formats) and deletes the named range
[quoted text clipped - 8 lines]
Next N
How do I clear or reset all range names once I am finished a
procedure?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1