Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating Text in Rows with COUNTA and CONCATENATE
Thank you!
This works perfectly and is good compact code. I actually got the COUNTA/CONCATENATE to work (counted the number of names, then depending on that value went through an IF/ELSIF to concatenate). Very,very cumbersome!! I was hoping was there was a streamlined way to do an array for these. My experience in Excel arrays is nil. But looking at your code - doesn't seem too scary! Thank you again - most grateful! Kindest Regards, NigelVII "Wigi" wrote: Hi Here's a VBA approach: Option Explicit Option Base 1 Sub stripafterwords() Dim r As Range Dim i As Integer Dim arrNames() As String For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp)) For i = 1 To Cells(r.Row, Columns.Count).End(xlToLeft).Column ReDim Preserve arrNames(i) arrNames(i) = Cells(r.Row, i).Value Next Cells(r.Row, 26).Value = Join(arrNames, Chr(10)) Next End Sub -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "NigelVII" wrote: I have data in rows like this: Sally Bob Dave Wally Jim Dave Henry Frank Ralph John Ringo George Paul Henry Bob There can be as many as 25 names. I am trying to find a way to CONCATENATE the list of names with a CHAR(10) between each name in order to have a list of names in a single cell that are each on its own line, with out any trailing blank lines in the cell. I have been trying to combine a COUNTA and a CONCATENATE without much luck. Can anyone provide some help with this or a different approach? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating certain rows only | Excel Worksheet Functions | |||
Manipulating this text. | Excel Programming | |||
Counta and rows.. | Excel Worksheet Functions | |||
Insert New Rows based on COUNTA() | New Users to Excel | |||
newbie: manipulating cols & rows in named range | Excel Programming |