Concatenate Text Strings over Array

[Katie]

Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red, Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using find/replace
to remove extraneous commas and slashes. But, because of the restrictions to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim cell As Range
Dim Sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = 1 To iLastCol
If .Cells(i, j).Value <> "/" Then
.Cells(i, iLastCol + 1).Value = _
.Cells(i, iLastCol + 1).Value & "," & _
.Cells(i, j).Value
End If
Next j
.Cells(i, iLastCol + 1).Value = _
Right(.Cells(i, iLastCol + 1).Value, Len(.Cells(i, iLastCol
+ 1).Value) - 1)
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

One way:

Public Function ConcatWithException( _
ByRef rng As Excel.Range) As String
Const csEXCEPTION As String = "/"
Const csDELIM As String = ", "
Dim rArea As Range
Dim rCell As Range
Dim sBuild As String
Dim sTemp As String
For Each rArea In rng
For Each rCell In rArea
sTemp = Trim(rCell.Text)
If sTemp <> csEXCEPTION Then _
If Len(sTemp) > 0 Then _
sBuild = sBuild & csDELIM & sTemp
Next rCell
Next rArea
If Len(sBuild) > 0 Then
ConcatWithException = Mid(sBuild, Len(csDELIM) + 1)
Else
ConcatWithException = vbNullString
End If
End Function

 

[Katie]

Bob-

How can I get this to only act on the specified range instead of the entire
sheet?

 

[Katie]

These are awesome- now I'm just having trouble finding exactly where to put
in my range and cell data.

Thank you both!

 

[Bob Phillips]

It does only work on the range in column A.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

Call the UDF like this

=ConcatWithException(A11)

 

[Katie]

I'm sorry to ask so many questions!

This is actually part of a 6 page macro- is there anyway to code it for
those specific cells that I can just place right into the existing code?

 

[JE McGimpsey]

One way:

Dim sTest As String
sTest = ConcatWithException(Range("A11"))