Concatenate Macro

[osaka78]

Dear friends
i have a text in cell e9,e10,e11 and e12 is blank
then text in e13,e14 and e15 is blank
then text in e16 and e17 is blank ..ect

how can i create a macro to CONCATENATE all cells before the blanck cell in
one cell

regards

 

[paul]

=E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
or text between the cell contents

 

[osaka78]

Dear paul
i need macro to do Concatenate
regards

=E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
or text between the cell contents

 

[Bryan Hessey]

Osaka,

What Paul gave you was the equivalant of Concatenate just say
=E9 & E10 & E11
etc, . . or
=E9&E10&E11
etc, . . or
=E9 & " " & E10 & " " & E11
etc, . . or
=E9&" "&E10&" "&E11
etc to put spaces between the concatenated cells.

Do you also have other cells to join? - and if so where are they?

--

 

[osaka78]

guys i know this but i need MACRO to do this for me

regards

 

[Gary''s Student]

Try this macro:


Sub osaka78()
Dim s As String
Dim i As Integer
Dim k As Long
k = 65536
s = ""
For i = 9 To 11
s = s & Cells(i, 5)
Next
MsgBox (s)
Cells(k, 1) = s
s = ""
For i = 14 To 15
s = s & Cells(i, 5)
Next
MsgBox (s)
Cells(k - 1, 1) = s
s = ""
For i = 16 To 16
s = s & Cells(i, 5)
Next
MsgBox (s)
Cells(k - 2, 1) = s
End Sub



It will concatenate the desired cells and place each concatenated group into
one cell.

 

[Bob Phillips]

Function ConcatFormat(rng1 As Range, ParamArray rng2()) As String
Dim i As Long
ConcatFormat = rng1.Text
For i = LBound(rng2) To UBound(rng2)
If Not rng2(i) Is Nothing Then _
ConcatFormat = ConcatFormat & rng2(i).Text
Next i
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)