I have created a utility that contains named constants used for data validation on a particular sheet. As an enhancement requirement from the users, they want to be able to copy another workbooks sheet(s) into the utility. While using some sample files provided from the users, I have encountered name constants are being copied into the utility.

There is a routine that I can write to eliminate unneeded name constants, while keeping the required ones?

How can I get a listing of the named constants listed in the Define Name dialog box and only those names (not the Refers To€�) along with the associated index

Any assistance would be greatly appreciated

In code, you can use:

Sub GetDefinedNames()

Dim n As Name

For Each n In ActiveWorkbook.Names
Debug.Print n.Name & " " & n.Index
Next

End Sub

Adapt to your needs.



--
Message posted from http://www.ExcelForum.com

Dim nm as Name, i as Long
i = 0
for each nm in Activeworkbook.Names
i = i + 1
msgbox "index: " & i & " name: " & nm.name
Next

However, if you are going to delete the names, you need to loop backward or
the indexes could change.

--
Regards,
Tom Ogilvy


"ilona" wrote in message
...
I have created a utility that contains named constants used for data
validation on a particular sheet. As an enhancement requirement from the
users, they want to be able to copy another workbook's sheet(s) into the
utility. While using some sample files provided from the users, I have
encountered name constants are being copied into the utility.

There is a routine that I can write to eliminate unneeded name constants,
while keeping the required ones?

How can I get a listing of the named constants listed in the Define Name
dialog box and only those names (not the Refers To") along with the
associated index?

Any assistance would be greatly appreciated

Thanks kknie and Tom for you help. Your solutions worked.

Sub DeleteCopiedNames(
Dim NameConstant As Nam

For Each NameConstant In ActiveWorkbook.Name
If NameConstant.Visible The
Debug.Print NameConstan
' NameConstant.Delet
End I
Next NameConstan

End Su

When I run this code, all the names appear include workbook name. Can you please explain to me why? It was my understanding that the visible method would only show the names that appear visible in the dialog box.