Delete Multiple Named Ranges 1

[binaryfingers]

Hi

I have a sheet that has over 100 named ranges on it. I would like to delete them all. However the only way I can find is selecting them 1 by 1 and hit delete. As you can appreciate, takes a while..

I dont know VBA code, so any help?

Thanks,

 

[SkipVought]

Hi,

sub DeleteAllNamesInWorkbook()
  dim n as name
  for each n in activeworkbook.names
    n.delete
  next
end sub

alt+F11 activates the VB Editor

cnt+R view the Project

right click and select Insert module

Paste code into Module

Run the code.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[binaryfingers]

Excellent! Thanks for the FAST response and the detailed answer!

 

[KenWright]

Just for info, there is a way to do it without VBA which i cribbed a while ago from an old post of Norman Harker's in the MS newsgroups:-

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
----------------------------------------------------------------------------

 

[binaryfingers]

Thanks Ken, I must be missing something as I cant find that option under options and transition

 

[KenWright]

If you are in 2003 then I'm afraid it has disappeared. It's there in XP.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------