Deleting Range Names in Excel

[ramnewbietoaccess]

I have a worksheet that has 122 ranges named. I need to delete all of them and rename them. I know how to delete one but i was wondering since there so many is there a faster way to delete it rather than "Name" highlight it and then delete one at a time

 

[DaleWatson123321]

Here you go...

Sub Delete_All_RangeNames()
num = ActiveWorkbook.Names.Count
For i = 1 To num
ActiveWorkbook.Names(1).Delete
Next i
End Sub

Regards, Dale Watson

 

[SkipVought]

Hi,

copy this code into a MODULE and run

for each n in names
  n.delete
next

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[ramnewbietoaccess]

thanks a lot i am sure glad i asked cuz that was gonna be crappy.

thanks!

 

[SkipVought]

If you use Dale's solution, be sure to correct the index

   ActiveWorkbook.Names([b][red]i[/red][/b]).Delete

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[ramnewbietoaccess]

I tried both suggestions and when i run the module it highlights the n and says variable not defined. i should add that i am using 2000 if that matters. any suggestion?

 

[SkipVought]

You have options explicit...

Dim n
for each n in names
  n.delete
next

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[KenWright]

If you didn't want to use VBA then the 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.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[ramnewbietoaccess]

oh my duh i didnt define the variable thanks that really saved me a day of monotony