iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = Cells(i,Columns.Count).End(xlToLeft).Column
For j = 2 to iLastCol
If cells(i,j).Value < "" Then
cells(I,1).Value = cells(i,1).Value & "|" & cells(i,j).Value
cells(i,j).Value =""
End If
Next j
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Hi all. I have a database style worksheet that has in column A a persons
name. In columns B through AZ are items that person is authorized to work
on (pulldown list). This data will be uploaded to an ERP sytem, and need
to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way to
programatically do this without actually using concatenate? It is also
very
possible that columns could be blank. As above, Joe has data in the 1st 3
columns, then skips a few, then again in 9. Ideally, before Upload I'd
like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.