VBA Consolidate Data

[jlclyde]

I am looking for a chunk of code that will take a range of multiple
cells and consolidate it into one cell seperated with commas.
Hopefully someone has this code just laying around.

Thanks
Jay

 

[Kevin B]

This concatenated values in Column A of a worksheet and placed the returned
value in cell B1.

It declares the current region as a range and uses the row count of the
range to determine the loop stop value

Starting at cell A1 it picks up the value in the cell and and uses the loop
counter (the variable l) as the offset value
Sub Concat()

Dim strVal As String
Dim ws As Worksheet
Dim r As Range
Dim lngOffset As Long
Dim lngStop As Long
Dim l As Long

Set ws = ThisWorkbook.Sheets(1)
Set r = ws.Range("A1").CurrentRegion
lngStop = r.Rows.Count


For l = 0 To lngStop
strVal = strVal & " " & ws.Range("A1"). _
Offset(l).Value & ","
Next l

ws.Range("B1").Value = Left$(strVal, Len(strVal) - 1)

Set ws = Nothing
Set r = Nothing

End Sub

 

[Gary''s Student]

Function pack_um(r As Range) As String
pack_um = ""
i = 1
For Each rr In r
If i = 1 Then
pack_um = rr.Value
i = 2
Else
pack_um = pack_um & "," & rr.Value
End If
Next
End Function

 

[jlclyde]

Function pack_um(r As Range) As String
pack_um = ""
i = 1
For Each rr In r
    If i = 1 Then
        pack_um = rr.Value
        i = 2
    Else
        pack_um = pack_um & "," & rr.Value
    End If
Next
End Function

--
Gary''s Student - gsnu200763






- Show quoted text -

I will give each of these a try and get back to the forum in a day or
two.
Thanks,
Jay