Turn Excel range into VBA string

Here's a quick way to test (Note: this will only work with Excel 2016 (or if you have the TextJoin() function).

First, in the empty column D, do =C1&"@", so you get your last column filled with the cell+@

Then, say in cell E1, =TEXTJOIN(",",TRUE,A1:C5) (Note: TRUE there means to skip blanks. If you have blanks, and want to keep them, change that to FALSE).

THen, on that cell, run

=Substitute(E1,"@,","@")

Or combine the formulas into one: =SUBSTITUTE(TEXTJOIN(",",TRUE,A1:C4),"@,","@").

If you need vba, just throw the formula into a VBA macro and run like that.

Here is a UDF that returns the desired output:

EDIT Changed to add EOL at the end.

Option Explicit
Function MultiJoin(Rng As Range, Delimiter As String, EOL As String) As String
    Dim V As Variant, W As Variant
    Dim COL As Collection
    Dim I As Long, J As Long

V = Rng
Set COL = New Collection
ReDim W(1 To UBound(V, 2))
For I = 1 To UBound(V, 1)
    For J = 1 To UBound(V, 2)
        W(J) = V(I, J)
    Next J
    COL.Add W
Next I

ReDim V(1 To COL.Count)
For I = 1 To COL.Count
    V(I) = Join(COL(I), Delimiter)
Next I

W = Join(V, EOL)
MultiJoin = W & EOL

End Function

One could shorten the code by using WorksheetFunctions, but I would guess execution time would be slower.

Shortened Code

Option Explicit
Function MultiJoin(Rng As Range, Delimiter As String, EOL As String) As String
    Dim V As Variant, W As Variant
    Dim I As Long, J As Long

V = Rng
With WorksheetFunction

For I = 1 To UBound(V, 1)
    V(I, 1) = Join(.Index(V, I, 0), Delimiter)
Next I
MultiJoin = Join(.Transpose(.Index(V, 0, 1)), EOL) & EOL

End With

End Function

This solution will require either a reference to the Microsoft Forms 2.0 Object Library in your project or some other way of fetching the contents of the clipboard (like through an API call).

Function TurnExcelRangeIntoVBAString(Optional cellDelimiter As String = ",", _
                                     Optional rowDelimiter As String = "@") _
         As String

    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    rng.Copy

    Dim clip As New MSForms.DataObject
    Dim txt As String
    clip.GetFromClipboard
    txt = clip.GetText()
    txt = Replace(Replace(txt, vbTab, cellDelimiter), vbCrLf, rowDelimiter)

    TurnExcelRangeIntoVBAString = txt
End Function

you could try this

Option Explicit

Sub main()
    Dim strng As String
    Dim cell As Range

    With Worksheets("TurnRangeIntoString") '<--| change "TurnRangeIntoString" to your actual worksheet name
        For Each cell In Intersect(.UsedRange, .Columns(1)) '<--| loop through its column 1 cells
            strng = strng & Join(Application.Transpose(Application.Transpose(.Range(cell, cell.End(xlToRight)).value)), ",") & "@" '<--| build string
        Next cell
    End With
    MsgBox strng
End Sub

Sub aquatique()
dim a(),s$,i&,j&:a=selection.value
for i=1 to ubound(a)
for j=1 to ubound(a,2)
    if j=1 then
        if i=1 then
            s=  a(i,j)
        else
            s=s &"@" & vbnewline & a(i,j)
        end if
    else
        s=s &";" & a(i,j)
    end if
next
next
end sub

simple but does the job. Slow on huge ranges, you'd need to use "join"

How about this?:

Sub Concatenate()
Dim Cel As Range, Rng As Range
Dim sString As String, r As Long, c As Long, r2 As Long

Set Rng = Selection
r = Selection.Row
c = Selection.Column
r2 = Selection.Row
For Each Cel In Rng
    r = Cel.Row
    If sString = "" Then
        sString = Cel.Value
        Else
            If r <> r2 Then sString = sString & "@" & Cel.Value
            If r = r2 Then sString = sString & "," & Cel.Value
    End If
    r2 = Cel.Row
Next

sString = sString & "@"
Debug.Print sString

End Sub