Removing Named Ranges
 

1. I have been passed a workbook which contains named ranges which are no
longer relevant or whose relative cells no longer exist, more than one
hundred of them.

I want to know how can I get rid of all of them easily rather than deleting
one by one and have error messages or names simply wont delete?

2. The same workbook sometimes does not allow me to insert rows or colums
and displays the message 'too many cells formats'

Can anyone help me overcome these problems.

Thanks for your usual help.

Removing Named Ranges
 

To delete all names use the following macro
Sub DeleteNames()
Dim nm As Name

For Each nm In Names
ActiveWorkbook.Names(nm.Name).Delete
Next

End Sub

For the second problem
lower the number of formats use...
Select a range, clear formatting for that range then do it in a minimal way

"WINS" wrote:

1. I have been passed a workbook which contains named ranges which are no
longer relevant or whose relative cells no longer exist, more than one
hundred of them.

I want to know how can I get rid of all of them easily rather than deleting
one by one and have error messages or names simply wont delete?

2. The same workbook sometimes does not allow me to insert rows or colums
and displays the message 'too many cells formats'

Can anyone help me overcome these problems.

Thanks for your usual help.

Removing Named Ranges
 

Will this code remove all the names? There are some which I have identified
as being valid and relevant to the current workbook.

"Sheeloo" wrote:

To delete all names use the following macro
Sub DeleteNames()
Dim nm As Name

For Each nm In Names
ActiveWorkbook.Names(nm.Name).Delete
Next

End Sub

For the second problem
lower the number of formats use...
Select a range, clear formatting for that range then do it in a minimal way

"WINS" wrote:

1. I have been passed a workbook which contains named ranges which are no
longer relevant or whose relative cells no longer exist, more than one
hundred of them.

I want to know how can I get rid of all of them easily rather than deleting
one by one and have error messages or names simply wont delete?

2. The same workbook sometimes does not allow me to insert rows or colums
and displays the message 'too many cells formats'

Can anyone help me overcome these problems.

Thanks for your usual help.

Removing Named Ranges
 

Yes, it will remove all names.

You can either check in the code...
IF (nm.Name < "your name" then
which will be cumbersome if you have many names to retain.

Get a list with the following method, keep only those you want to retain,
run the macro to remove all and then create again from the list (Highlight
your range including the headings and go to InsertNameCreate...)

To obtain a list of all Named Ranges and where they refer, select any blank
cell (make sure you have no data underneath or 1 column over) and go to
InsertNamePaste then Paste List.


"WINS" wrote:

Will this code remove all the names? There are some which I have identified
as being valid and relevant to the current workbook.

"Sheeloo" wrote:

To delete all names use the following macro
Sub DeleteNames()
Dim nm As Name

For Each nm In Names
ActiveWorkbook.Names(nm.Name).Delete
Next

End Sub

For the second problem
lower the number of formats use...
Select a range, clear formatting for that range then do it in a minimal way

"WINS" wrote:

1. I have been passed a workbook which contains named ranges which are no
longer relevant or whose relative cells no longer exist, more than one
hundred of them.

I want to know how can I get rid of all of them easily rather than deleting
one by one and have error messages or names simply wont delete?

2. The same workbook sometimes does not allow me to insert rows or colums
and displays the message 'too many cells formats'

Can anyone help me overcome these problems.

Thanks for your usual help.

Removing Named Ranges
 

Thank you, this shall definitely be of help

"Sheeloo" wrote:

Yes, it will remove all names.

You can either check in the code...
IF (nm.Name < "your name" then
which will be cumbersome if you have many names to retain.

Get a list with the following method, keep only those you want to retain,
run the macro to remove all and then create again from the list (Highlight
your range including the headings and go to InsertNameCreate...)

To obtain a list of all Named Ranges and where they refer, select any blank
cell (make sure you have no data underneath or 1 column over) and go to
InsertNamePaste then Paste List.


"WINS" wrote:

Will this code remove all the names? There are some which I have identified
as being valid and relevant to the current workbook.

"Sheeloo" wrote:

To delete all names use the following macro
Sub DeleteNames()
Dim nm As Name

For Each nm In Names
ActiveWorkbook.Names(nm.Name).Delete
Next

End Sub

For the second problem
lower the number of formats use...
Select a range, clear formatting for that range then do it in a minimal way

"WINS" wrote:

1. I have been passed a workbook which contains named ranges which are no
longer relevant or whose relative cells no longer exist, more than one
hundred of them.

I want to know how can I get rid of all of them easily rather than deleting
one by one and have error messages or names simply wont delete?

2. The same workbook sometimes does not allow me to insert rows or colums
and displays the message 'too many cells formats'

Can anyone help me overcome these problems.

Thanks for your usual help.