Large Concatenate

[Steph]

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.

 

[Bob Phillips]

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]

Thanks Bob. Can I ask one more small favor? Turns out I need one more
column "concatenated". Is your code easy to modify to concatenate ColA,
ColB and then the 50 or so columns? Thank you!!!!

 

[Bob Phillips]

Steph,

It should already concatenate however many columns there are

Bob

 

[Steph]

Understood. Thanks Bob!

Steph,

It should already concatenate however many columns there are

Bob