philip1127
New Member
- Joined
- Jul 29, 2002
- Messages
- 2
Hi - I'm trying to concatenate a range of cells, (A1:A250). Is there any way to accomplish this without typing (A1&A2&A3...&A250)? Thanks so much.
-Philip
-Philip
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
For Each cel In rng
x = x & cel.Value & ", "
Next
.Range("K1").Value = x
End With
End Sub
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
Dim myString As String
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
For Each cel In rng
x = x & cel.Value & ", "
Next
.Range("K1").Value = Left(x, Len(x) - 2)
End With
End Sub
ok. I researched & finally figured it out for those that may need this in the future. This code will concatenate a Range (column d in this example) with ", " & then take out the ", " on the last one. The result is provided in "K1".
This may be a bit late... but with a slight revision I have this working simply to remove the extra comma at the end:
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
Dim y As String
With ActiveSheet
Set rng = .Range("A2:A" & Range("A65536").End(xlUp).Row)
For Each cel In rng
x = x & cel.Value & ", "
y = Len(x)
Next
.Range("B1").Value = Left(x, y - 2)
End With
End Sub