[need vba] transpose data to multiple column into one column

[muhammad susanto]

hi guys..

could you help me, how to figure out this problem using vba code (transpose multiple column into one column):

sample data (in col.a)

COL.A	B	C	D	E
ACTIVE	PT BANK	JOHN MAC OBAMA	2014.12.00025	MEDAN STREET # 10
ACTIVE	PT AGUNG	LEE DONALD	2014.12.00965	SAHANA STREET 8
etc..

<tbody>
</tbody>

after macro :

col.G

ACTIVE

PT BANK

JOHN MAC OBAMA

2014.12.00025

MEDAN STREET # 10

(BLANK ROW/SPACE)

PT AGUNG

LEE DONALD

2014.12.00965

SAHANA STREET 8

etc...

<tbody>
</tbody>

any assistance, will be appreciated...

m.susanto

 

[kevatarvind]

hi may be this help you

before macro

Sheet1

*	A	B	C	D	E
1	ACTIVE	PT BANK	JOHN MAC OBAMA	2014.12.00025	MEDAN STREET # 10
2	ACTIVE	PT AGUNG	LEE DONALD	2014.12.00965	SAHANA STREET 8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:71px;"><col style="width:126px;"><col style="width:93px;"><col style="width:129px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4




after macro

Sheet1

*	A	B	C	D	E	F	G
1	ACTIVE	PT BANK	JOHN MAC OBAMA	2014.12.00025	MEDAN STREET # 10	*	ACTIVE
2	ACTIVE	PT AGUNG	LEE DONALD	2014.12.00965	SAHANA STREET 8	*	PT BANK
3	*	*	*	*	*	*	JOHN MAC OBAMA
4	*	*	*	*	*	*	2014.12.00025
5	*	*	*	*	*	*	MEDAN STREET # 10
6	*	*	*	*	*	*	*
7	*	*	*	*	*	*	ACTIVE
8	*	*	*	*	*	*	PT AGUNG
9	*	*	*	*	*	*	LEE DONALD
10	*	*	*	*	*	*	2014.12.00965
11	*	*	*	*	*	*	SAHANA STREET 8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:71px;"><col style="width:126px;"><col style="width:93px;"><col style="width:129px;"><col style="width:64px;"><col style="width:129px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Option Explicit
Sub Multiple_Col_To_One()
Dim lr As Integer
Dim i As Integer
Dim j As Integer
j = 2
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
    Range(Cells(i, 1), Cells(i, 5)).Copy
    Cells(Rows.Count, 7).End(xlUp).Offset(2 - j, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    j = 0
Next
Cells(1, 7).Select
End Sub

 

[muhammad susanto]

hi kevatarvind, it works great!!!

thanks you very much...