PDA

View Full Version : [SOLVED] Concantonate Cells



austenr
05-27-2005, 06:57 AM
This routine works for me but what I need to do is have a space between each word. For example, "Frank Jones Sam Bush". Not, "FrankJonesSamBush". How do you add a space in between words? :dunno


Sub concateColA()
Dim start As Long
Dim lastRow As Long
start = 1
lastRow = [A65536].End(xlUp).Row
Do
Range("F" & start).Value = _
Range("A" & start).Value & Range("B" & start).Value & _
Range("C" & start).Value & Range("D" & start).Value & _
Range("E" & start).Value
start = start + 1
Loop Until start > lastRow
End Sub

Bob Phillips
05-27-2005, 07:21 AM
This routine works for me but what I need to do is have a space between each word. For example, "Frank Jones Sam Bush". Not, "FrankJonesSamBush". How do you add a space in between words?



Sub concateColA()
Dim i As Long
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Range("F" & i).Value = Trim( _
Range("A" & i).Value & " " & Range("B" & i).Value & " " & _
Range("C" & i).Value & " " & Range("D" & i).Value & " " & _
Range("E" & i).Value)
Next i
End Sub

austenr
05-27-2005, 07:28 AM
That got it thanks. Solved