Bob,

On the 13th Janaury you were kind enough to provide me with a function that
concatenated values across approx 70 columns for a dynamic number of rows.

That code is posted below.

Since then l have been testing the workbook, before releasing it, and have
now found a problem. The user can enter a 3 digit code in any of the 70
columns up to a maximum of 15. Therefore the maximum number of digits that
can be concatenated is 45. However when that number of digits is
concatenated the result ends up as a number raised to a power.(The function
works fine when only 4 or 5 sets of 3 digits are entered)

ie these digits when concatenated

100
101
200
201
203
219
225
300
301
400
401
500
550
600
900

end up being displayed as

100101200201203000000000000000000000000000000

and the value displayed in the formula bar is

1.00101200201203E+44

what l really need to see is a string as follows

100101200201203219225300301400401500550600

because in the next 15 columns l extract each of the 3 digit codes using
functions (for example Left(A1,3) Mid(A1,4,3) etc)

Any ideas as to whats going wrong and what the solution is please?

Alternatively is it possible to 'loop thru' each of the 70 columns and when
the 1st 3 digit value is found put it into say column 100, the 2nd 3 digit
value in column 101 etc for each row in the dynamic range?

xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx

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

Kind regards

Michael Beckinsale