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" wrote in message
...
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 ,
Katie 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" wrote in message
...
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 ,
Katie 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" wrote in message
...
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" wrote in message
...
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(A1:D1)

In article ,
Katie 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(A1:D1)

In article ,
Katie 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("A1:D1"))


In article ,
Katie 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(A1:D1)