Combine rows

Hello,

I've got a problem with excel. I want to join rows automaticaly just like in this example:

From this:

Col 1 Col 2 Col 3 Col 4 ...
Row 1: bla bla bla bla
Row 2: ooo ooo ooo ooo
Row 3: 333 333 333 333
...
...
...

To this:

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9 Col 10 ...
Row0: bla bla bla bla ooo ooo ooo ooo 333 333 ...



How can I do this with a macro? I'm very thankful for any help I can get. Thanks!

try this macro. the result will be in sheet2

Sub test()
Dim j As Integer, k As Integer
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
j = .Range("a1").End(xlDown).Row
For k = 1 To j
Range(.Cells(k, 1), .Cells(k, 1).End(xlToRight)).Copy
Worksheets("sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial
Next k
End With

Application.CutCopyMode = False


End Sub

Hey!

Thanks it kinda worked! What if i want to have the data in a column instead of one row in sheet 2? How can I do this automaticaly with just one push of a button? Man I really need to learn visual basic to simplify use of excel, it would save alot of time.

Thankful for all replies