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)...

Answer 1

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.

Answer 2

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); 

Answer 3

.. 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.