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."

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)

"Katie" <(E-Mail Removed)> wrote in message
news:C38B038A-785C-4132-A921-(E-Mail Removed)...
> 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."
>
>
>
>

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


In article <C38B038A-785C-4132-A921-(E-Mail Removed)>,
Katie <(E-Mail Removed)> wrote:

> 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-

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

"Bob Phillips" wrote:

> 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)
>
> "Katie" <(E-Mail Removed)> wrote in message
> news:C38B038A-785C-4132-A921-(E-Mail Removed)...
> > 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."
> >
> >
> >
> >
>
>
>

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

Thank you both!

"JE McGimpsey" wrote:

> 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
>
>
> In article <C38B038A-785C-4132-A921-(E-Mail Removed)>,
> Katie <(E-Mail Removed)> wrote:
>
> > 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."
>

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)

"Katie" <(E-Mail Removed)> wrote in message
news:296DA153-6BB5-4690-BB6E-(E-Mail Removed)...
> Bob-
>
> How can I get this to only act on the specified range instead of the
> entire
> sheet?
>
> "Bob Phillips" wrote:
>
>> 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)
>>
>> "Katie" <(E-Mail Removed)> wrote in message
>> news:C38B038A-785C-4132-A921-(E-Mail Removed)...
>> > 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."
>> >
>> >
>> >
>> >
>>
>>
>>

Call the UDF like this

=ConcatWithException(A11)

In article <B415B8E6-13C3-4918-B6B9-(E-Mail Removed)>,
Katie <(E-Mail Removed)> wrote:

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

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" wrote:

> Call the UDF like this
>
> =ConcatWithException(A11)
>
> In article <B415B8E6-13C3-4918-B6B9-(E-Mail Removed)>,
> Katie <(E-Mail Removed)> wrote:
>
> > These are awesome- now I'm just having trouble finding exactly where to put
> > in my range and cell data.
>

One way:

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


In article <CCD06C87-2B27-48F0-A928-(E-Mail Removed)>,
Katie <(E-Mail Removed)> wrote:

> 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" wrote:
>
> > Call the UDF like this
> >
> > =ConcatWithException(A11)