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?