Concatenate cells from M9 to Last row in M

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
 
P

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top