Concatenate cells from M9 to Last row in M
 

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

Concatenate cells from M9 to Last row in M
 

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 wrote:
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

Concatenate cells from M9 to Last row in M
 

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" wrote in message
...
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