Populate the Concatenate function using VBA

[Michael Beckinsale]

Hi All,

Bit of a strange one this!

I need to concatenate several columns (actually > 70) and wondered if the
Concatenate Function could be populated using VBA.

Does anybody have the code to do this or able to point me in the right
direction please ?

I realise that the function is limited to 30 columns but l could do the
concatenate 3 times then concatenate them.

TIA

Michael Beckinsale

 

[Bob Phillips]

For i = 1 To 70
myValue = myValue & Cells(Activecell.Row,i)
Next i

'then do something with myValue

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Andrew Taylor]

Option Explicit
Function ConcatenateRange(rSource As Range) As String
Dim rCell As Range
For Each rCell In rSource
ConcatenateRange = ConcatenateRange & rCell.Text
Next
End Function

Then in the worksheet you can type e.g. =ConcatenateRange(A1:BR1)

hth
Andrew Taylor

 

[Michael Beckinsale]

Bob & Andrew,

Many thanks for your prompt replies, both of which do the job as described
very well.

As the concatenation of >70 columns needs to be applied to anything between
100 and 5000 rows l am inclined to use Andrews solution rather than using
Bob's and looping thru the rows.

Can either of you see a problem in that?

Again many thanks

Michael

 

[Bob Phillips]

I would go one stage further, do it all in one batch macro.

This works from row 1 to the last row and concatenates values from column 2
out, and drop the result in column A. Change to suit

Sub ConcatenateRange()
Dim val As String
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long
Dim j As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
val = ""
For j = 2 To iLastCol
val = val & Cells(i, j)
Next j
Cells(i, "A").Value = val
Next i
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Michael Beckinsale]

Bob,

That looks great. Will try it out over the weekend as l have to go out now
and will let you know how l got on.

Again many thanks to both of you.

Michael