Concatenate cells from M9 to Last row in M

[BEEJAY]

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

 

[Paul Robinson]

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

 

[Rick Rothstein]

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