how to identify built-in Names

[Eric]

I want to clear out any user defined names in the names collection, without
touching built-in names like Print_Area, etc.

Should this be done through the workbooks name collection, or the worksheets
name collection; and more importantly, how do I identify a built-in my code.

Thanks, Eric

 

[Vasant Nanavati]

I would not consider Print_Area to be a built-in name. When you set a print
area, you are, in fact, naming it yourself.

Having said that, you caould try something like the following:

Sub DeleteNames()
Dim nm As Name
For Each nm In ThisWorkbook.Names
If Right(nm.Name, 12) <> "Print_Titles" And _
Right(nm.Name, 10) <> "Print_Area" Then _
nm.Delete
Next
End Sub

 

[Bob Phillips]

Why not clear them all out, Print_Area etc. will get re-created as required.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Vasant Nanavati]

Hi Bob:

I guess if the OP has specific print areas (possibly with repeated titles)
on a number of worksheets where entire worksheets are not to be printed, it
would require doing page setups all over again for each sheet. At least,
that's what I thought!

Regards,

Vasant

 

[Eric Fingerhut]

That was my thought too Vasant. I don't want to force the user to
recreate usefull settings if I don't have to.

Your technique is ok for handling Print related names, but there are
other built in names to, so it would be a lot nice if there was a way to
identify them, similar to the way a contol has a built-in property.

Thanks, Eric

 

[Vasant Nanavati]

What other "built-in" names are you referring to? Can you give an example?

 

[Peter T]

Hi Eric,

There are quite a few of these built in names, or rather names used by
Excel. Eg, "FilterDatabase" is a hidden sheet level name created with an
Auto filter and could cause minor problems if deleted.

Apart from these names it would be an idea to check any range name you are
about to delete has no precedents or dependants, which can be done
programmatically. But there might be others such as named formulas.

However I wouldn't want to delete names in user's workbook. Instead I would
recommend user to use the "Name Manager" utility by Jan Karel Pieterse and
colleagues:

http://www.jkp-ads.com/ navigate to the utilities page

Regards,
Peter T

 

[Charles Williams]

Name Manager has an option to hide or show system names.

The filters we use to identify system names are as follows:

If sName Like "*_FilterDatabase" Then
If sName Like "*Print_Area" Then
If sName Like "*Print_Titles" Then
If sName Like "*.wvu.*" Then
If sName Like "*wrn.*" Then
If sName Like "*!Criteria" Then

There may be more ...


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Eric]

Thanks Charles - very useful!

Name Manager has an option to hide or show system names.

The filters we use to identify system names are as follows:

If sName Like "*_FilterDatabase" Then
If sName Like "*Print_Area" Then
If sName Like "*Print_Titles" Then
If sName Like "*.wvu.*" Then
If sName Like "*wrn.*" Then
If sName Like "*!Criteria" Then

There may be more ...


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com