Large Concatenate - (RP)

[Steph]

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

 

[Bob Phillips]

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]

Hi Bob,

Close. Let me re-write what you said:

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

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

M-BJ are the cells that the user uses a pick list to select. In Sheet2
A5:A1000 are the "picks" that they can pick from (The data validation list
source). So if the user wanted to select every pick from the list, I wanted
to allow them to simply select ALL in M10, rather than slelcting each item
one at a time in columns M:BJ.

Thanks Bob!

 

[Steph]

Hi Bob,

Close. Let me re-write what you said:

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

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

M-BJ are the cells that the user uses a pick list to select. In Sheet2
A5:A1000 are the "picks" that they can pick from (The data validation list
source). So if the user wanted to select every pick from the list, I wanted
to allow them to simply select ALL in M10, rather than slelcting each item
one at a time in columns M:BJ.

Thanks Bob!