delete names or rename

[kjschari]

hai

i want to delete tne name ranges in a work sheet.

or give a code to delete all the names or to replace them auto.

 

[Don Guillett]

try these

sub deleteshtnames()
For Each Name In Sheets("Data").Names
Name.Delete
Next Name
end sub

Sub DeleteAllNames()
For Each Name In Names
Name.Delete
Next Name
End Sub

Sub AddName2()
ActiveSheet.Names.Add Name:="MyRange2", RefersTo:="=" & Selection.Address()
End Sub

' To add a range name based on a selection using a variable. Note: This is a
shorter version
Sub AddName3()
Dim rngSelect As String
rngSelect = Selection.Address
ActiveSheet.Names.Add Name:="MyRange3", RefersTo:="=" & rngSelect
End Sub

' To add a range name based on a selection. (The shortest version)
Sub AddName4()
Selection.Name = "MyRange4"
End Sub

 

[Ken Wright]

Previous post of Norman Harker's, that will give you a very quick way to get rid of them all.


Simplest way is to use an old Lotus keystroke command that works in
Excel but which doesn't have an equivalent without VBA.

Tools > Options > Transition
Select the Lotus 1-2-3 Help option

Thereafter:

/RNR

Will clear all of your names including those set by Excel such as
Print_Area.

But watch out. There's no, "Are you sure!" And there's no Undo
capability. But if this is what you want, it sure beats doing it one
at a time.

A more user friendly approach is used by Rob Bovey in Excel Utilities
1.5 obtainable from:

Rob Bovey
http://www.appspro.com/

Apart from this clearance of all names there's a lot more other useful
"everyday" utilities included.

 

[Dave Peterson]

If this is a tool to add your your collection of utilities, then I'd get a copy
of an addin written by Jan Karel Pieterse, Charles Williams and Matthew Henson.

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

(If it's code you need to incorporate into your macro, this won't help. But
it's still worth getting.)