Concatenate values of more cells in a single variable in vba

Question

I have an excel file with four columns: name, surname, address, area. There are a lot of rows. Is there a way to concatenate all the values of every single row in a variable, using vba?

I need a variable that should contain something like this:

(name1, surname1, address1, area1); (name2, surname2, address2, area2); (name3, surname3, address3, area3)...

[is this what you're looking for?](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba) — cybernetic.nomad, Apr 06 '22 at 14:35
Just a single string variable? Where each value is concatenated? — JvdV, Apr 06 '22 at 14:44
@BigBen I want to write a kind of query, more or less. In every row I have the values, so I'm trying to take the values and write something like "Insert values (values1, values2, values3...)" etc. — ZeMatteis, Apr 06 '22 at 14:44
Use `&` to concatenate? Or maybe [`Join`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/join-function)? Hopefully you're not designing something vulnerable to SQL injection btw... — BigBen, Apr 06 '22 at 14:49
Hard to tell what exactly you after, but a whole bunch of `Replace()` functions could get you places? `Replace("(" & Mid(Replace(Replace(Application.ArrayToText([A1:D3], 1), Chr(34), ""), ";", ");("), 2), "}", ")")` where your dummy data was in `A1:D3`? Much better solutions out there, with loops etc. But it's best if you have a go at it yourself and report back any issues code-wise. — JvdV, Apr 06 '22 at 14:52

Pᴇʜ · Answer 1 · 2022-04-06T16:41:14.793

3

If you have the following data in your worksheet

Then the following code will read the data into an array …

Option Explicit

Public Sub Example()
    
    Dim RangeData() As Variant  ' declare an array
    
    RangeData = Range("A1:D5").Value2  ' read data into array
    
End Sub

… with the following structure:

Alternatively you can do something like

Public Sub Example()   
    Dim DataRange As Range
    Set DataRange = Range("A2:D5")
    
    Dim RetVal As String
    
    Dim Row As Range
    For Each Row In DataRange.Rows
        RetVal = RetVal & "(" & Join(Application.Transpose(Application.Transpose(Row.Value2)), ",") & "); "
    Next Row

    Debug.Print RetVal
End Sub

To get this output:

(name1, surname1, address1, area1); (name2, surname2, address2, area2); (name3, surname3, address3, area3); (name4, surname4, address4, area4);

edited Apr 06 '22 at 16:41

answered Apr 06 '22 at 14:43

Pᴇʜ

56,719
10
49
73

I considered the array, because basically is near to what I need... But is there a way to write the result like a sort of list that shows all the values of the cells of the range? – ZeMatteis Apr 06 '22 at 15:00
1

@ZeMatteis See my edit for an alternative solution. – Pᴇʜ Apr 06 '22 at 16:41
1

If you dispose of Excel vers. MS365 you might also use `Evaluate("ArrayToText(A2:D5, 1)")` (arg. 1 = strict) resulting in the following output string: `{"name1","surname1","address1","area1";"name2","surname2","address2","area2";"name3","surname3","address3","area3";"name4","surname4","address4","area4"}` @Pᴇʜ ` – T.M. Apr 06 '22 at 17:12

T.M. · Answer 2 · 2022-04-06T17:49:46.597

.. is there a way to write the result like a sort of list that shows all the values of the cells of the range?

Yes, there is. In addition to PEH's valid answers and disposing of Excel version MS365 you might also use

    Dim s as String
    s = Evaluate("ArrayToText(A2:D5, 1)") ' arg. value 1 representing strict format

resulting in the following output string:


{"name1","surname1","address1","area1";"name2","surname2","address2","area2";"name3","surname3","address3","area3";"name4","surname4","address4","area4"}

Syntax

ARRAYTOTEXT(array, [format])

The ARRAYTOTEXT function returns an array of text values from any specified range. It passes text values unchanged, and converts non-text values to text.

The format argument has two values, 0 (concise default format) and 1 (strict format to be used here to distinguish different rows, too):

Strict format, i.e. value 1 includes escape characters and row delimiters. Generates a string that can be parsed when entered into the formula bar. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.

Isn't there a `WorksheetFunction` for `ArrayToText` so you don't need to use `Evaluate`? Not familiar with MS365. Would be nicer to handle in VBA. — Pᴇʜ, Apr 07 '22 at 05:54
@Pᴇʜ, yes there is. See the comment section underneath OP's question. — JvdV, Apr 07 '22 at 11:51
@Pᴇʜ You can use either the `WorksheetFunction` or late bound `Application.ArrayToText` as alternative to evaluation. Note that a tabular formula entry (e.g. `"."` for Germany) would use possibly different row list separators compared to any of the above VBA solutions using `","` within the row groups. — T.M., Apr 07 '22 at 16:35

score 0 · Accepted Answer · answered Apr 07 '22 at 09:33

Thank you for your answers, suggestions, ideas and hints. I am sorry if my question was not so clear, all the solutions you added were perfect and extremely elegant.

In the end I found a way - a dumber way in comparison to all the things you wrote - and I solved with a for statement.

I did like this:

totRow = ActiveSheet.UsedRange.Rows.Count

 For i = 1 To totRow

    name = Cells(i, 1)
    surname = Cells(i, 2)
    address = Cells(i, 3)
    area = Cells(i, 4)

    Example = Example & "(" & name & ", " &  surname & ", " & address & ", " & area & "); "
    
 Next i

Range("E1").Value = Example

It works (it does what I wanted to do), but I noticed a little limit: if the rows are a lot I can't keep the whole text in the variable.

Concatenate values of more cells in a single variable in vba

3 Answers3