Capturing Define Names

[Guest]

Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

 

[Bob Phillips]

Dim nme As Name
Dim i As Long

For Each nem In ActiveWorkbook.Names
i = i + 1
Cells(i, "A").Value = nme.Name
Cells(i, "B").Value = nme.RefersTo
Next i


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Bob, you forgot something

Dim nme As Name
Dim i As Long

i = 1
Cells(i,"A").value = "Name"
Cells(i,"B").value = "RefersTo"
For Each nme In ActiveWorkbook.Names
i = i + 1
Cells(i, "A").Value = nme.Name
Cells(i, "B").Value = nme.RefersTo
Next nme



Barb Reinhardt

 

[Francois via OfficeKB.com]

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.

Thanks Chip, Very useful

 

[Guest]

Chip: Good Post. What if you want to return a subset of the range names?

I have a workbook that has about 100 names in it. I need to extract a
subset, say all names beginning with "INPUT_", and use them in a loop to
clear data or autofill.

Is this possible?

 

[Chip Pearson]

Try something like


Sub AAA()

Dim NM As Name
For Each NM In ThisWorkbook.Names
If StrComp(Left(NM.Name, Len("INPUT_")), "INPUT_", vbTextCompare) = 0
Then
' name begins with "INPUT_". Do something with NM
End If
Next NM

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Charles Williams]

Chip's method is good: or if you are doing much work with defined names you
can download Name Manager from
http://www.decisionmodels.com/downloads.htm

This free addin allows you to filter names by many different criteria,
You can also list the filtered names on a worksheet, edit them and then
re-import them into one or more workbooks


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Guest]

Chip: Thanks for the quick response.

I will try it and let you know how it works.

Tom