Combing Many Cells into One

[codeman_not]

Hi. I a have a sheet in which Cells A1 to A50 have a text value. In cell B1, I want to combine the contents of all those cells (A1:A50). I know I can use a formula using "&", but then I have to input each cell (50 times in my case). Is there a simpler way to do this?

Thanks

 

[stnkynts]

Run this:

Sub Combine()
Dim icell As Range
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For Each icell In Range("A1:A" & lastrow)
    If Not IsEmpty(icell) Then
        Range("B1") = Range("B1") & icell
    End If
Next icell
End Sub

Or use =CONCATENATE(A1,A2,A3) etc