Populate the Concatenate function using VBA
 

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

Populate the Concatenate function using VBA
 

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)

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

Populate the Concatenate function using VBA
 

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

Populate the Concatenate function using VBA
 

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

Populate the Concatenate function using VBA
 

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

Populate the Concatenate function using VBA
 

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
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
"Michael Beckinsale" wrote in message
...
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