How can I convert a range to a string (VBA)?

Question

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting as possible (i.e. it needs to look like a table or list, not just a string of characters). I've tried working with CStr(), as well as converting from a range to an array and then to a string, but I just get errors.

Edit: Code attempt

Dim email_answer As Integer
email_answer = MsgBox("Do you want to be emailled a copy of this schedule?", vbYesNo)
If email_answer = vbYes Then

    Dim wb As Workbook
    Dim to_send As Range
    to_send = Range("D3", "D10")

    If Val(Application.Version) < 14 Then Exit Sub

    Set wb = ActiveWorkbook
    With wb
        MailFromMacWithMail body content:=CStr(to_send), _
                    mailsubject:="Schedule", _
                    toaddress:="email address", _
                    ccaddress:="", _
                    bccaddress:="", _
                    attachment:=.FullName, _
                    displaymail:=False
    End With
    Set wb = Nothing
End If

Sorry @ShaiRado Code attempt added now (this was the last attempt, have also tried range->array->string method). — 1937827, Jan 21 '17 at 11:08

Wolfie · Accepted Answer · 2017-05-08T16:46:59.967

9

To make a comma separated list of cell values in a range:

Function RangeToString(ByVal myRange as Range) as String
    RangeToString = ""
    If Not myRange Is Nothing Then
        Dim myCell as Range
        For Each myCell in myRange
            RangeToString = RangeToString & "," & myCell.Value
        Next myCell
        'Remove extra comma
        RangeToString = Right(RangeToString, Len(RangeToString) - 1)
    End If
End Function

You could add extra functionality like inserting a semicolon instead of a comma if the row number increases.

To use this function:

Sub AnySubNameHere()
    Dim rng As Range
    Set rng = ActiveSheet.Range("A3:A10")

    Dim myString as String
    myString = RangeToString(rng)
End Sub

edited May 08 '17 at 16:46

answered Jan 21 '17 at 10:13

Wolfie

27,562
7
28
55

Sorry for the novice question. I added your code and then called it using `Dim to_send As Range` `to_send = Range("D3", "D10")` `Call RangeToString(to_send)` Will this change to_send to a string? My code still returned a byref argument mismatch error when I did this – 1937827 Jan 21 '17 at 11:00
I have edited my answer to show you how to use this function. The function returns a string object, so you can just assign that to a variable :) – Wolfie Jan 21 '17 at 11:13
you are amazing, thank you so much! Is there a way to make it run down each column before moving to the next row? At the moment it appears to be reading A1,B1,C1,A2,B2,C2 etc. instead of A1,A2,A3,B1,B2,B3 etc. – 1937827 Jan 21 '17 at 11:48
You could loop through the columns `For Each rowRange in myRange.Rows` then loop through cells in each `rowRange` instead of in the whole of `myRange`. Note you should `Dim` the Range object `myRow` first – Wolfie Jan 21 '17 at 13:50
Thanks, but now it's given me a runtime error 13 type mismatch. Did I implement your solution correctly? `Dim myCell As Range Dim RowRange As Range For Each RowRange In myRange.Rows For Each myCell In RowRange RangeToString = RangeToString & Chr(13) & myCell.Value Next myCell Next RowRange` – 1937827 Jan 21 '17 at 15:34
You can change the "," into chr(13) so you can have it displayed with newline – Brandon Jake Sullano Dec 13 '17 at 10:27

score 7 · Answer 2 · answered Jan 21 '17 at 10:21

7

you could use this function:

Function Rang2String(rng As Range) As String
    Dim strng As String
    Dim myRow As Range
    With rng
        For Each myRow In .Rows
            strng = strng & Join(Application.Transpose(Application.Transpose(myRow.value)), "|") & vbLf
        Next
    End With
    Rang2String = Left(strng, Len(strng) - 1)
End Function

which would return a string with linefeed character as range rows separator and pipes ("|") as columns separator

answered Jan 21 '17 at 10:21

user3598756

28,893
4
18
28

Good use of Transpose twice to convert 2d array to 1d array (something I've only just learnt from SO)! – S Meaden Jan 21 '17 at 14:29
@SMeaden, it's needed for "rows" ranges while for "columns" ones you only need one transpose – user3598756 Jan 21 '17 at 14:33

score 3 · Answer 3 · answered Dec 05 '17 at 15:45

I know this question is already almost a year old, but I found a quick solution that works for me: You do have to create a reference to Microsoft Forms 2.0 Object Library to use the DataObject.

Public Function GetStringFromRange(RNG As Range) As String
    Dim DOB As New MSForms.DataObject
    RNG.Copy
    DOB.GetFromClipboard
    GetStringFromRange = DOB.GetText
End Function

Nathan Sutherland · Answer 4 · 2021-07-06T17:30:12.670

2

No need to iterate.

Application.Textjoin(Chr(10),TRUE,Range("D3", "D10"))

or

Join(Application.Transpose(Range("D3:D10")),Chr(10))

edited Jul 06 '21 at 17:30

answered Jul 06 '21 at 17:23

Nathan Sutherland

1,191
7
9

score 1 · Answer 5 · answered May 18 '18 at 21:24

1

There is a much easier way. Assuming the variable rng is a range, then writing:

rng = Left(rng,Len(rng))

will miraculously turn rng into a string.

answered May 18 '18 at 21:24

user9813775

21
1

No it does not... what is the input `rng` here? What do you think the output `rng` is? The question is about converting the contents of a range (over multiple cells) to a string variable, not formatting the contents of a single cell as a string... – Wolfie May 29 '18 at 09:00

score 0 · Answer 6 · answered Jan 28 '17 at 17:41

Any one of these functions will do it for you.

Function ConCatRange2(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
Next
ConCatRange2 = Left(sbuf, Len(sbuf) - 1)
End Function

OR

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
    If k <> 1 Then
        mergem = mergem & "," & rr.Value
    End If
    k = 2
Next
End Function

OR

Function spliceUm(r As Range) As String
spliceUm = ""
For Each rr In r
spliceUm = spliceUm & rr.Value & ";"
Next
End Function

score 0 · Answer 7 · edited Aug 31 '18 at 01:47

0

You can use the following solution to convert a range to a string in VBA:

Sub convert()

Dim rng As Range, cell As Range

Dim filter As String

filter = ""

Set rng = Selection

For Each cell In rng

    If Not cell Is Nothing Then
        filter = """" & cell & """" & "," & filter
    End If

Next cell

End Sub

edited Aug 31 '18 at 01:47

Grant Miller

27,532
16
147
165

answered Aug 30 '18 at 18:59

Lerisson Felipe Pereira

1

please explain on answers what you are doing and why this is a good approach – mischva11 Aug 30 '18 at 19:40

score 0 · Answer 8 · answered Aug 06 '19 at 14:37

0

Given the apparent need to iterate the range, I'd imagine it'd be considerably quicker to copy the range to an array first, and build the string by looping the array.

answered Aug 06 '19 at 14:37

Rohan Moore

1

score 0 · Answer 9 · answered Feb 28 '20 at 10:00

This does the job, even with the Range in another Worksheet

Function RangeToString(ByVal myRange As range) As String
    RangeToString = ""
    If Not myRange Is Nothing Then
        RangeToString = "=" & myRange.Worksheet.Name & "!" & myRange.Address
    End If
End Function

score 0 · Answer 10 · answered Jun 09 '22 at 12:38

Here is a code, where I take into account for each rows to be enter as lines when application.transpose doesn't work for me.

Function RangeToString(ByVal myRange As Range) As String
RangeToString = ""
If Not myRange Is Nothing Then
    Dim myCell As Range
    Dim myRow As Range
    
    For Each myRow In myRange.Rows
        For Each myCell In myRow.Cells
        RangeToString = RangeToString & " " & myCell.Value
     Next myCell
        RangeToString = RangeToString & vbCr
    Next myRow
 
    'Remove extra space
    RangeToString = Right(RangeToString, Len(RangeToString) - 1)
End If 
End Function

How can I convert a range to a string (VBA)?

10 Answers10

Linked