View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_6_] Bob Phillips[_6_] is offline
external usenet poster
 
Posts: 11,272
Default 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