Greetings:
Have variable length of cells in column M, to be concatenated and entered in
C (3 rows below last row).

Trying:

Range("C:C" & LastRow + 3) ' Cell to receive concatenated result

Then need something like:

=Concatenate(M9&M10&M11 etc - to last row.

Column M already contains the data with required commas and spaces inserted,
ready to do the required searches in an "in house" program.

Thank you

Hi
WrapIt can be called within another sub or used in a worksheet.
rng can be any range object e.g.
set rng = Range("M3:M30")
set rng = Selection
set rng = Range("M3").CurrentRegion

and many others. See Excel VBA (Greene et al) or some such book for a
full discussion of range.


Public Function WrapIt(rng As Range) As String
Dim myCell As Range
Dim Temp As String
Temp = ""
For Each myCell In rng
Temp = Temp & CStr(myCell)
Next myCell
WrapIt = Temp
End Function

regards
Paul

On Feb 25, 4:31*pm, BEEJAY <BEE...@discussions.microsoft.com> wrote:
> Greetings:
> Have variable length of cells in column M, to be concatenated and enteredin
> C (3 rows below last row).
>
> Trying:
>
> Range("C:C" & LastRow + 3) * ' Cell to receive concatenated result
>
> Then need something like:
>
> =Concatenate(M9&M10&M11 etc *- to last row.
>
> Column M already contains the data with required commas and spaces inserted,
> ready to do the required searches in an "in house" program.
>
> Thank you

Give this macro a try...

Sub CombineColumnM()
Dim LastRowC As Long, LastRowM As Long
LastRowC = Cells(Rows.Count, "C").End(xlUp).Row
LastRowM = Cells(Rows.Count, "M").End(xlUp).Row
Cells(LastRowC + 3, "C").Value = Join(WorksheetFunction.Transpose( _
Range("M9:M" & LastRowM)), "")
End Sub

--
Rick (MVP - Excel)


"BEEJAY" <(E-Mail Removed)> wrote in message
newsE5C46B0-5B3D-4257-8F17-(E-Mail Removed)...
> Greetings:
> Have variable length of cells in column M, to be concatenated and entered
> in
> C (3 rows below last row).
>
> Trying:
>
> Range("C:C" & LastRow + 3) ' Cell to receive concatenated result
>
> Then need something like:
>
> =Concatenate(M9&M10&M11 etc - to last row.
>
> Column M already contains the data with required commas and spaces
> inserted,
> ready to do the required searches in an "in house" program.
>
> Thank you