getting value of named range

[greg]

I have a named range. Lets say it goes over lots of cells.
A1 to E20

Can I get the values out as a string? Single string.

I have tried some things like:

excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value

any ideas

 

[Aviashn]

Is there a particular order that it needs to follow? What do the
cells hold?

 

[Lars-Åke Aspelin]

I have a named range. Lets say it goes over lots of cells.
A1 to E20

Can I get the values out as a string? Single string.

I have tried some things like:

excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value

any ideas

try this:

Function all_values_in_one_string(r As Range) As String
Dim s As String
For Each c In r
s = s & c.Value
Next c
all_values_in_one_string = s
End Function

Hope this helps / Lars-Åke

 

[Per Jessen]

I have a named range.  Lets say it goes over lots of cells.
A1 to E20

Can I get the values out as a string?  Single string.

I have tried some things like:

excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value

any ideas

Hi

You need to loop through the cells. Look at this macro, it will loop
through the cells row by row.

Sub NamedRangeToString()
Dim nString As String
For Each c In Range("MyRange").Cells
nString = nString & c.Value
Next
MsgBox nString
End Sub

Regards,
Per

 

[Ron Rosenfeld]

I have a named range. Lets say it goes over lots of cells.
A1 to E20

Can I get the values out as a string? Single string.

I have tried some things like:

excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value

any ideas

Not enough data to be sure what you really want, but brute force should work:

=====================
Sub foo()
Dim c As Range
Dim a As String

For Each c In Range("MyRange")
a = a & c.Value
Next c
Debug.Print a
End Sub
====================
--ron

 

[Bob Phillips]

Dim myArray

myArray = Application.Transpose(Range("myRange"))
MsgBox myArray(1)

 

[greg]

thanks all,
I will look into the looping