Concatenate all cells in row A

[DanG]

I have values in column A and I would like to concatenate all thiose values into B1. So that all the values from column A are combined in one cell (b1).

I would imagine it would be using VBA (which I do not know, but am willing to give it a go) and would use a "foreach" loop.

Any help would help.

Thank you

 

[DanG]

OK,
I found...

Function Concat(myRange As Range, Optional myDelimiter As String)
    Dim r As Range

    Application.Volatile
    For Each r In myRange
        Concat = Concat & r & myDelimiter
    Next r
    If Len(myDelimiter) > 0 Then
        Concat = Left(Concat, Len(Concat) - Len(myDelimiter))
    End If
End Function

It works just fine. But...
When I go to use it I can't just type "=contat(A1:A65)" I have to call it using "=PERSONAL.XLS!Concat(A1:A13,", ")" Not very user friendly. I know I could go to "functions > userdifined" but would rather not.

Any ideas?

 

[unmarkedhelicopter]

I wouldn't put it in personal I'd put it in the actual workbook, specifically the worksheet concerned (so it's only for that worksheet (faster).
I wouldn't have it as a function but as a sub.
I hate volatile.
I'd call it from worksheet change event if the change was from the required sheet and only if it affected column 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Const myDelimiter As String = "" ' optional delimiter
    Dim rCell As Range, SStrng As String
    If Target.Column <> 1 Then Exit Sub
    For Each rCell In Range("A1:A" & ActiveSheet.Rows.Count)
      If rCell.Value <> "" Then SStrng = SStrng & rCell.Value & myDelimiter
    Next rCell
    If Len(myDelimiter) > 0 Then SStrng = Left(SStrng, Len(SStrng) - Len(myDelimiter))
    Range("B1").Value = SStrng
End Sub

I know the target is hard coded but you were VERY specific about where you wanted it. Ultimately it depends on what else is on the sheet and whether speed is an issue.

 

[whitespace]

well said helicopter

 

[DanG]

Yours seemed to do the trick.
And thank you for the education.