Getting the values in a named range

[vsoler]

Hi,

I want to list, in the inmediate windows, the values of the named
ranges in my workbook. I prefer, if possible, not having to specify
the worksheet names.

I've tried the following code:

Sub abc()
Dim a
For Each n In Names
Debug.Print n, n.Name
a = n.Value
Debug.Print a
Next
End Sub

I get:

=sdf!$D$6:$D$8 casa
=sdf!$D$6:$D$8
=sdf!$C$12:$D$14 mesa
=sdf!$C$12:$D$14

however I would like something like

=sdf!$D$6:$D$8 casa
1
2
3
=sdf!$C$12:$D$14 mesa
7
8
9

is this possible?

Thank you

 

[JLGWhiz]

Names are children of the workbook. The refersto: data is to identify where
within the workbook the name is used. That is the basics of the Names
collection. Your part two illustration is confusing, since the debug print
will only provide a predetermined print format.

 

[Gary Keramidas]

i use this

Sub name_ranges() ' dumps all named ranges
Dim nm As Name
For Each nm In ThisWorkbook.Names
Debug.Print nm.Name
Next
End Sub

 

[Gary Keramidas]

misread the post

 

[vsoler]

Names are children of the workbook.  The refersto: data is to identify where
within the workbook the name is used.  That is the basics of the Names
collection.  Your part two illustration is confusing, since the debug print
will only provide a predetermined print format.

JLGWhiz,

Thank you for your answer, but if I want to put the values of the
cells in a Variant, how do I do it?