converting vertical value to horizontal within braces

**lovinguy** · 10-15-2011, 10:35 AM

Hi!

I need to convert the vertical row column into a single Cell in a special format.Attached file can explain you better what I really want.

Thanks in advance.

Lovinguy

**arlu1201** · 10-15-2011, 10:42 AM

If there are not too many such entries, you can obtain it like this

=A2&"','"&A3&"','"&A4&"','"&A5&"','"&A6&"','"&A7

**MarvinP** · 10-15-2011, 10:43 AM

Hi Lovinguy,

You could do this manuallly with the concatinate function

=A2&","&A3&","&A4&","&A5&","&A6&","&A7

or use VBA Join function.
see
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

If the range of your rows changes then VBA is the way to go.

hope this helps.

**jaslake** · 10-15-2011, 04:05 PM

Hi lovinguy

Try this

Option Explicit
Sub PutItTogether()
    Dim LR As Long
    Dim Rng As Range
    Dim Cell As Range
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A2:A" & LR)
    For Each Cell In Rng
        If Cell.Row = 2 Then
            Range("B2").Value = Cell.Value
        Else
            Range("B2").Value = Range("B2").Value & "','" & Cell.Value
        End If
    Next Cell
    Range("B2").Value = Range("B2").Value & "'"
'    Range("A2:A" & LR).ClearContents
End Sub

If you want column A cleared uncomment this line