convert different rows to one column

[abokhadiga]

Hello!

I'd like to convert the following rows:
1 2
7 9
5 6

to one column:
1
2
7
9
5
6

Any ideas? Even in VB?
TYIA!

 

[venkat1926]

Sub test()
Dim r As Range, c As Range
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
Range(c, c.End(xlToRight)).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
End With
Next c
Application.CutCopyMode = False
End Sub

 

[abokhadiga]

Thank you venkat1926!

I've tried the following code and it worked great for me:

Sub ToOneColumn()
Dim i As Long, k As Long, j As Integer
Application.ScreenUpdating = False
Columns(1).Insert
i = 0
k = 1
While Not IsEmpty(Cells(k, 2))
j = 2
While Not IsEmpty(Cells(k, j))
i = i + 1
Cells(i, 1) = Cells(k, j)
Cells(k, j).Clear
j = j + 1
Wend
k = k + 1
Wend
Application.ScreenUpdating = true
End Sub

The problem has been solved!

 

[mirabeau]

Hi abokhadiga,

Please enjoy the forum.

You solved your problem to your own satisfaction with your own code.
That's very good!

I'd just like to note however, for your possible interest, that there are much shorter and faster ways to solve this type of problem.

For example vba code along these general lines

Sub xyz()
Dim e, k
For Each e In Range("A1").CurrentRegion
    k = k + 1
    Cells(k, "d") = e
Next
End Sub