Concatenate an entire column into one cell? Module needed?

[raceonusa]

Basically what I need to do is the equivalent of typing this in once cell...

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11... etc...

Is there an easy way to type this, or a formula that can do the same thing?

I've tried =A:A but this adds the total, I need to concatenate them

 

[Rick Rothstein]

I wish it did. The cells it is concatenating are an If statement looking at a pivot table. So when the cells change the concatenation should change. The only way to get it to change is when I click on the cell and hit enter in the formula box again.

I have not tested out Mike's code, but from your description... does adding this line at the beginning of the code make it work for that situation?

Application.Volatile

 

[Ingolf]

You could use a UDF. For your case the formula would be =ConcatRange(A:A)

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    With inputRange
        If Not (Application.Intersect(.Parent.UsedRange, .Cells) Is Nothing) Then
            For Each oneCell In Application.Intersect(.Parent.UsedRange, .Cells)
                If oneCell.Text <> vbNullString Then
                    ConcatRange = ConcatRange & delimiter & oneCell.Text
                End If
            Next oneCell
            ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
        End If
    End With
End Function

Excellent!!! This UDF can concatenate even noncontiguous cells.