software

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

=E9amp;E10amp;E11 or =e9amp;quot; quot;amp;E10amp;quot; quot;amp;E11amp;quot; and E12 is blankquot; if you want spaces and
or text between the cell contents
--
paul
remove nospam for email addy!
quot;osaka78quot; wrote:

gt; Dear friends
gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; then text in e13,e14 and e15 is blank
gt; then text in e16 and e17 is blank ..ect
gt;
gt; how can i create a macro to CONCATENATE all cells before the blanck cell in
gt; one cell
gt;
gt; regards

Dear paul
i need macro to do Concatenate
regards

quot;paulquot; wrote:

gt; =E9amp;E10amp;E11 or =e9amp;quot; quot;amp;E10amp;quot; quot;amp;E11amp;quot; and E12 is blankquot; if you want spaces and
gt; or text between the cell contents
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;osaka78quot; wrote:
gt;
gt; gt; Dear friends
gt; gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; gt; then text in e13,e14 and e15 is blank
gt; gt; then text in e16 and e17 is blank ..ect
gt; gt;
gt; gt; how can i create a macro to CONCATENATE all cells before the blanck cell in
gt; gt; one cell
gt; gt;
gt; gt; regards


Osaka,

What Paul gave you was the equivalant of Concatenate just say
=E9 amp; E10 amp; E11
etc, . . or
=E9amp;E10amp;E11
etc, . . or
=E9 amp; quot; quot; amp; E10 amp; quot; quot; amp; E11
etc, . . or
=E9amp;quot; quot;amp;E10amp;quot; quot;amp;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:
gt; Dear paul
gt; i need macro to do Concatenate
gt; regards
gt;
gt; quot;paulquot; wrote:
gt;
gt; gt; =E9amp;E10amp;E11 or =e9amp;quot; quot;amp;E10amp;quot; quot;amp;E11amp;quot; and E12 is blankquot; if you want
gt; spaces and
gt; gt; or text between the cell contents
gt; gt; --
gt; gt; paul
gt; gt; remove nospam for email addy!
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;osaka78quot; wrote:
gt; gt;
gt; gt; gt; Dear friends
gt; gt; gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; gt; gt; then text in e13,e14 and e15 is blank
gt; gt; gt; then text in e16 and e17 is blank ..ect
gt; gt; gt;
gt; gt; gt; how can i create a macro to CONCATENATE all cells before the blanck
gt; cell in
gt; gt; gt; one cell
gt; gt; gt;
gt; gt; gt; regards--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531289guys i know this but i need MACRO to do this for me

regards

quot;Bryan Hesseyquot; wrote:

gt;
gt; Osaka,
gt;
gt; What Paul gave you was the equivalant of Concatenate just say
gt; =E9 amp; E10 amp; E11
gt; etc, . . or
gt; =E9amp;E10amp;E11
gt; etc, . . or
gt; =E9 amp; quot; quot; amp; E10 amp; quot; quot; amp; E11
gt; etc, . . or
gt; =E9amp;quot; quot;amp;E10amp;quot; quot;amp;E11
gt; etc to put spaces between the concatenated cells.
gt;
gt; Do you also have other cells to join? - and if so where are they?
gt;
gt; --
gt;
gt; osaka78 Wrote:
gt; gt; Dear paul
gt; gt; i need macro to do Concatenate
gt; gt; regards
gt; gt;
gt; gt; quot;paulquot; wrote:
gt; gt;
gt; gt; gt; =E9amp;E10amp;E11 or =e9amp;quot; quot;amp;E10amp;quot; quot;amp;E11amp;quot; and E12 is blankquot; if you want
gt; gt; spaces and
gt; gt; gt; or text between the cell contents
gt; gt; gt; --
gt; gt; gt; paul
gt; gt; gt; remove nospam for email addy!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;osaka78quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Dear friends
gt; gt; gt; gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; gt; gt; gt; then text in e13,e14 and e15 is blank
gt; gt; gt; gt; then text in e16 and e17 is blank ..ect
gt; gt; gt; gt;
gt; gt; gt; gt; how can i create a macro to CONCATENATE all cells before the blanck
gt; gt; cell in
gt; gt; gt; gt; one cell
gt; gt; gt; gt;
gt; gt; gt; gt; regards
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=531289
gt;
gt;

Try this macro:Sub osaka78()
Dim s As String
Dim i As Integer
Dim k As Long
k = 65536
s = quot;quot;
For i = 9 To 11
s = s amp; Cells(i, 5)
Next
MsgBox (s)
Cells(k, 1) = s
s = quot;quot;
For i = 14 To 15
s = s amp; Cells(i, 5)
Next
MsgBox (s)
Cells(k - 1, 1) = s
s = quot;quot;
For i = 16 To 16
s = s amp; 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 Studentquot;osaka78quot; wrote:

gt; guys i know this but i need MACRO to do this for me
gt;
gt; regards
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Osaka,
gt; gt;
gt; gt; What Paul gave you was the equivalant of Concatenate just say
gt; gt; =E9 amp; E10 amp; E11
gt; gt; etc, . . or
gt; gt; =E9amp;E10amp;E11
gt; gt; etc, . . or
gt; gt; =E9 amp; quot; quot; amp; E10 amp; quot; quot; amp; E11
gt; gt; etc, . . or
gt; gt; =E9amp;quot; quot;amp;E10amp;quot; quot;amp;E11
gt; gt; etc to put spaces between the concatenated cells.
gt; gt;
gt; gt; Do you also have other cells to join? - and if so where are they?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; osaka78 Wrote:
gt; gt; gt; Dear paul
gt; gt; gt; i need macro to do Concatenate
gt; gt; gt; regards
gt; gt; gt;
gt; gt; gt; quot;paulquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =E9amp;E10amp;E11 or =e9amp;quot; quot;amp;E10amp;quot; quot;amp;E11amp;quot; and E12 is blankquot; if you want
gt; gt; gt; spaces and
gt; gt; gt; gt; or text between the cell contents
gt; gt; gt; gt; --
gt; gt; gt; gt; paul
gt; gt; gt; gt; remove nospam for email addy!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;osaka78quot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dear friends
gt; gt; gt; gt; gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; gt; gt; gt; gt; then text in e13,e14 and e15 is blank
gt; gt; gt; gt; gt; then text in e16 and e17 is blank ..ect
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; how can i create a macro to CONCATENATE all cells before the blanck
gt; gt; gt; cell in
gt; gt; gt; gt; gt; one cell
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; regards
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=531289
gt; gt;
gt; gt;

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 amp; rng2(i).Text
Next i
End Function--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;osaka78quot; gt; wrote in message
...
gt; Dear friends
gt; i have a text in cell e9,e10,e11 and e12 is blank
gt; then text in e13,e14 and e15 is blank
gt; then text in e16 and e17 is blank ..ect
gt;
gt; how can i create a macro to CONCATENATE all cells before the blanck cell
in
gt; one cell
gt;
gt; regards