concatenate 500 cells

[velvet]

Does anyone know how you can easily concatenate the
contents of 500 cells?

 

[Ken Wright]

One way, and assuming you wanted commas in between each record :-

If A1 is part of your range, just put another one in that isn't.

Sub Concat()
Dim i As String
i = "'"
j = Selection.Cells.Count
k = 0

For Each cell In Selection
k = k + 1
If k < j Then
i = i & cell & ","
Else
i = i & cell
End If
Next cell

Range("A1").Value = i
End Sub