[SOLVED] Concatenate Macro

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

=E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
or text between the cell contents
--
paul
remove nospam for email addy!



"osaka78" wrote:

> 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

Dear paul
i need macro to do Concatenate
regards

"paul" wrote:

> =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
> or text between the cell contents
> --
> paul
> remove nospam for email addy!
>
>
>
> "osaka78" wrote:
>
> > 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

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

regards

"Bryan Hessey" wrote:

>
> 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 Wrote:
> > Dear paul
> > i need macro to do Concatenate
> > regards
> >
> > "paul" wrote:
> >
> > > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want
> > spaces and
> > > or text between the cell contents
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "osaka78" wrote:
> > >
> > > > 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
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=531289
>
>

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.


--
Gary's Student


"osaka78" wrote:

> guys i know this but i need MACRO to do this for me
>
> regards
>
> "Bryan Hessey" wrote:
>
> >
> > 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 Wrote:
> > > Dear paul
> > > i need macro to do Concatenate
> > > regards
> > >
> > > "paul" wrote:
> > >
> > > > =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want
> > > spaces and
> > > > or text between the cell contents
> > > > --
> > > > paul
> > > > remove nospam for email addy!
> > > >
> > > >
> > > >
> > > > "osaka78" wrote:
> > > >
> > > > > 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
> >
> >
> > --
> > Bryan Hessey
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> > View this thread: http://www.excelforum.com/showthread...hreadid=531289
> >
> >

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)

"osaka78" <osaka78@discussions.microsoft.com> wrote in message
news:F6B05866-0D69-42FB-B08D-C038447B233E@microsoft.com...
> 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