I have modified the code from duane to the following, hope this helps.

1. It removes the 1000 row limit (and will use less memory in the process)
2. Will allow empty rows and columns to exist within the extent of the range
3. Ignores empty columns by stepping over them (does not add a comma after
empty cell)

Sub concat()
'istring is a concatenated strings
' assumes raw data starts in row 1
Dim istring As String, i As Long, j As Integer, erow As Long, icol As
Integer
erow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 4).EntireColumn.Insert
For i = 1 To erow
istring = ""
icol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 5 To icol
If j = 5 Then istring = Cells(i, j).Value Else _
If Len(Trim(Cells(i, j))) 0 Then istring = istring & " , "
& Cells(i, j).Value
Next j
Cells(i, 4) = istring
Next i
Columns(4).Columns.AutoFit
End Sub

--
Cheers
Nigel



"sonic" wrote in
message ...

Sorry, I probably didn't explain myself properly.
After further testing, I have discovered that the script stops as soon
as it reaches an empty cell.
I have realised now that sometimes there can be data in a row, after an
empty cell.
Is it possible for the macro to keep searching the entire row, until it
finds all data within that row, even if it hits an empty cell and keeps
going?

If it makes it any easier, can it keep searching to say, column 100?
That would be Plenty.

Thanks again


--
sonic
------------------------------------------------------------------------
sonic's Profile:
http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698