Convert data From Vertical to Horizontal Format

[reyrey]

How do you create macro to have move multiple numeric data from a vertical format to horizontal format using the plus (+) sign to separate each numeric data that does not have minus (-) sign. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>Problem is: <o></o>
<o> </o>-1
-2<o></o>
-3<o></o>
0<o></o>
2<o></o>
<o> </o>
Results required will be: <o></o>
<o> </o>-1-2-3+0+2<o></o>
<o> </o>
<o> </o>Any advice or help will be greatly appreciated...Thank you<o></o>

 

[rassten]

Maybe something like this:

Sub a_test()
Range(Range("A2"), Range("A2").End(xlDown)).NumberFormat = "+#;-#;+0"

Range(Range("A2"), Range("A2").End(xlDown)).Copy
Range("B2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
Application.CutCopyMode = False
End Sub

 

[Silvrhelm]

Hi, this will work by just highlighting all the cells that you want to change to horizontal from vertical and activating the macro, no need to worry about what cells the entries are in.

Sub VertHor()
    Dim i As Integer
    Dim N As Integer
    N = Application.WorksheetFunction.count(Selection)
    For i = 1 To N
        ActiveCell.Offset(i, 0).Select
        Selection.Cut
        ActiveCell.Offset(-i, i).Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, -i).Select
    Next i
End Sub

Hmm I think maybe I misread, you want the answer to all come out in a single cell? Well, I guess ill leave this here anyway but the post above is probably what you need.

 

[reyrey]

Thanks for the reply!

Both codes work great but how would you get the outcome to a single cell instead of multiple cells?

 

[rassten]

With headline in A1 and data from A2 and down

Sub a_2_test()

fr1 = Cells(Rows.Count, 1).End(xlUp).Row
Range(Range("A2"), Range("A2").End(xlDown)).NumberFormat = "+#;-#;+0"

For i = 2 To fr1
    con = con & Range("a" & i).Text
Next
Range("B2") = con

End Sub