Concatenate cells from M9 to Last row in M

  • Thread starter Thread starter BEEJAY
  • Start date Start date
B

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
 
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
 
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
 
Back
Top