Concatenate values of more cells in a single variable in vba-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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