Column to vblf delimited text

[AlexanderBB]

I want to convert a column of data into a string, with every row delimited with vblf.

My logic is to create an array like
Arr = .Range("A2:A" & .Cells(Rows.count, "A").End(xlUp).Row)
and then use join (Arr, vblf)

But Arr is 2 dimensional and Join doesn't work.
How might I best get round this ? Or is some other approach better?

 

[Alex Blakenburg]

As long as your number of rows is under the 65,536 limit of Transpose, you could use something like this:

Sub testConcatenate()

    Dim Arr As Variant
    Dim delimString As String
    
    With ActiveSheet
        Arr = Application.Transpose(.Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row))
    End With
    
    delimString = Join(Arr, vbLf)

End Sub

 

[AlexanderBB]

Thanks Alex, that worked nicely. A Big help.

 

[Alex Blakenburg]

You're welcome.