delete non-used range names
 

I have an excel 2003 file with multiple sheets, and hndreds of named range
referring to both single cells or ranges. I have renamed some ranges but the
old names persist. I know I could just delete all of the names in a workbook
and I have the code for that, but how would i check to see if a range name is
used anywhere, and if not then delete it?

Appreciate any help as always,

Jason

delete non-used range names
 

Jason,
This may help to list all the names:
http://j-walk.com/ss/excel/files/namelist.htm

As for seeing which are referenced, I suppose you have a few option;
- .Find through all formulae on all sheets to see if "RangeName" is used.
- Use something based on Auditing, maybe see if this fails:
Range("RangeName").NavigateArrow(True,1).Select

Obviously neither will tell you if VBA code uses any of the names. You would
have use the VBE and search the code for instances of the name.
http://www.cpearson.com/excel/vbe.htm

NickHK

"TxRaistlin" wrote in message
...
I have an excel 2003 file with multiple sheets, and hndreds of named range
referring to both single cells or ranges. I have renamed some ranges but
the
old names persist. I know I could just delete all of the names in a
workbook
and I have the code for that, but how would i check to see if a range name
is
used anywhere, and if not then delete it?

Appreciate any help as always,

Jason

delete non-used range names
 

Jason,
Something like:
Dim DependentCount as Long
On Error Resume Next
DependentCount=Range("RangeName).Dependents.Count
If DependentCount=0 Then
'Name not used on same sheet
End If
DependentCount=0

This does not seem to include dependents on other sheets, so for that you
may have to resort to .navigatearrow etc.

NickHK

"TxRaistlin" wrote in message
...
I have an excel 2003 file with multiple sheets, and hndreds of named range
referring to both single cells or ranges. I have renamed some ranges but
the
old names persist. I know I could just delete all of the names in a
workbook
and I have the code for that, but how would i check to see if a range name
is
used anywhere, and if not then delete it?

Appreciate any help as always,

Jason

delete non-used range names
 

Hi Jason,

To find if a Name is used in cell formulas, chart (series, titles etc),
'linked' in a textbox, in vba code in any open accessible project, and
various other places, and report such locations is virtually impossible.
Luckily all that and much more has been virtually done!

Get hold of the NameManager addin which you can get from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).

www.jkp-ads.com
www.DecisionModels.com

This is a new version, I did notice one or two minor bugs which I have no
doubt will be fixed soon. But it's a must have if you work extensively with
Names.

Regards,
Peter T



"TxRaistlin" wrote in message
...
I have an excel 2003 file with multiple sheets, and hndreds of named range
referring to both single cells or ranges. I have renamed some ranges but
the
old names persist. I know I could just delete all of the names in a
workbook
and I have the code for that, but how would i check to see if a range name
is
used anywhere, and if not then delete it?

Appreciate any help as always,

Jason