getting value of named range

  • Thread starter Thread starter greg
  • Start date Start date
G

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
 
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
Click to expand...


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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top