I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.

Hi,

Right click a sheet tab, view code and paste this in and run it

Sub Kill_Names()
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

Mike

"Nimesh" wrote:

I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.

Hi Mike,
I tried it, but when I run it, the VB shows error 400

"Mike H" wrote:

Hi,

Right click a sheet tab, view code and paste this in and run it

Sub Kill_Names()
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

Mike

"Nimesh" wrote:

I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.

Hi,

I can't reproduce that error.

Mike

"Nimesh" wrote:

Hi Mike,
I tried it, but when I run it, the VB shows error 400

"Mike H" wrote:

Hi,

Right click a sheet tab, view code and paste this in and run it

Sub Kill_Names()
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

Mike

"Nimesh" wrote:

I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.

I wouldn't delete all the names in a workbook without some thought.

Excel creates names (usually hidden) when you run certain features. I think
some wrn.* names are created when you use Report Manager. (I've seen them in
other workbooks/worksheets when I never used Report Manager, too.)

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.oaltd.co.uk/mvp

It'll help you decide what you want to do with the name.

Nimesh wrote:

I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.

--

Dave Peterson

Hi Mike,
Your macro is working fine. Thanks buddy. I'll try to found out the reason
why its not happening on particular machine. It worked very smoothly on other
machine.
Again thanks.

"Mike H" wrote:

Hi,

I can't reproduce that error.

Mike

"Nimesh" wrote:

Hi Mike,
I tried it, but when I run it, the VB shows error 400

"Mike H" wrote:

Hi,

Right click a sheet tab, view code and paste this in and run it

Sub Kill_Names()
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

Mike

"Nimesh" wrote:

I have an excel file that has many defined names (appx more than 100), is
there any way to remove all defined name at one go.