Concatenation in VB
 

Hello,

I'm looking for a script to customize that will concatenize the data in
Columns Q,R, S, and T into column P inserting a " ; " between each
piece. Sometimes there won't be data in column Q, R, S, or T. There
was an earlier post regarding this, but I can't seem to customize it to
do the columns I want.

This is the thread

http://www.excelforum.com/showthread.php?t=387698

Could someone please help? Even just adding comments to the code from
the other thread would be great so I can learn what all is going on.
I'm a very basic programmer.

Thanks!


--
mab586
------------------------------------------------------------------------
mab586's Profile: http://www.excelforum.com/member.php...o&userid=26059
View this thread: http://www.excelforum.com/showthread...hreadid=393987

Concatenation in VB
 

Hi mab586,

Here's some code that should do what you're looking for:

Sub demo()
Dim lRow As Long
Dim lMaxRow As Long
Dim nCol As Integer
Dim sConcat As String

With ActiveSheet
lMaxRow = Application.WorksheetFunction.Max( _
.Cells(.Rows.Count, 17).End(xlUp).Row, _
.Cells(.Rows.Count, 18).End(xlUp).Row, _
.Cells(.Rows.Count, 19).End(xlUp).Row, _
.Cells(.Rows.Count, 20).End(xlUp).Row)

For lRow = 2 To lMaxRow
sConcat = vbNullString
For nCol = 17 To 20
sConcat = sConcat & ";" & .Cells(lRow, nCol).Value
Next nCol
If Len(sConcat) Then .Cells(lRow, 16).Value = Mid$(sConcat, 2)
Next lRow
End With
End Sub


If you don't want the ";" character when there is no value in a particular
cell, you'd have to change sConcat to something like this:

sConcat = sConcat & IIf(Len(.Cells(lRow, nCol).Value), _
";" & .Cells(lRow, nCol).Value, vbNullString)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


mab586 wrote:
Hello,

I'm looking for a script to customize that will concatenize the data
in Columns Q,R, S, and T into column P inserting a " ; " between each
piece. Sometimes there won't be data in column Q, R, S, or T. There
was an earlier post regarding this, but I can't seem to customize it
to do the columns I want.

This is the thread

http://www.excelforum.com/showthread.php?t=387698

Could someone please help? Even just adding comments to the code from
the other thread would be great so I can learn what all is going on.
I'm a very basic programmer.

Thanks!

Concatenation in VB
 

Sub concat()
'istring is a concatenated strings
' assumes raw data starts in row 1
Dim istring As String, i As Long, j As Integer, erow As Long
Dim Qrow as Long, Rrow as Long, Srow as Long
Dim Trow as Long
Integer
Qrow = Cells(Rows.Count, 1).End(xlUp).Row
Rrow = Cells(Row.count,1).End(xlup).Row
Srow = Cells(rows.count,1).end(xlup).Row
Trow = Cells(rows.count,1).End(xlup).Row
eRow = Application.Max(Qrow,Rrow,Srow,Trow)

For i = 1 To erow ' rows 1 to the last filled row
istring = ""
For j = 17 To 20 ' columns Q to T
If Len(Trim(Cells(i, j).Value)) 0 Then
If Len(istring) = 0 Then
istring = Trim(Cells(i, j).Value)
Else
istring = istring & " ; " & Trim(Cells(i, j).Value)
End If
End If
Next j
Cells(i, "P") = istring
Next i
Columns(16).Columns.AutoFit
End Sub

--
Regards,
Tom Ogilvy


"mab586" wrote in
message ...

Hello,

I'm looking for a script to customize that will concatenize the data in
Columns Q,R, S, and T into column P inserting a " ; " between each
piece. Sometimes there won't be data in column Q, R, S, or T. There
was an earlier post regarding this, but I can't seem to customize it to
do the columns I want.

This is the thread

http://www.excelforum.com/showthread.php?t=387698

Could someone please help? Even just adding comments to the code from
the other thread would be great so I can learn what all is going on.
I'm a very basic programmer.

Thanks!


--
mab586
------------------------------------------------------------------------
mab586's Profile:
http://www.excelforum.com/member.php...o&userid=26059
View this thread: http://www.excelforum.com/showthread...hreadid=393987