Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Concatenate - (RP)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Concatenate - (RP)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Concatenate - (RP)
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! "Bob Phillips" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Concatenate - (RP)
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! "Bob Phillips" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large list of numbers to concatenate | Excel Worksheet Functions | |||
Concatenate large numbers of cells | Excel Discussion (Misc queries) | |||
Help with CONCATENATE! | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Large Concatenate | Excel Programming |