Let me see that I understand correctly.

If M10 say is "ALL", then that line will be concatenated to the contents of
cells A-L, and then the contents of A5:A1000, the rest of M-BJ is ignored.

If M10 is not "ALL", we just concatgenate A-BJ as before.

Is that correct?

--

HTH

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


"Steph" wrote in message
...
Thanks to Bob Philips, I have the bit of code below. It takes 50 columns
of
data, and essentially concatenates each populated cell within a row, with
a
| in between each one. My quesion is this - If the contents of the first
column of data (M) is the word "ALL", I would like the code to concatenate
all populated cells from a table on sheet1 (A5:A1000).

So basically, columns M through BJ are pick lists. Rather than having the
user pick 50 items 1 at a time, if the code sees ALL in the forst column,
it
will take all data from the data validation list and concatenate them. Is
this possible? Thanks!

iLastRow = Cells(Rows.Count, "B").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