multi columns to one

[aminexcel]

i have this three columns:

<table style="border-collapse: collapse; width: 162pt;" border="0" cellpadding="0" cellspacing="0" width="216"><col style="width: 54pt;" span="3" width="72"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt; width: 54pt;" align="left" height="15" width="72">A</td> <td class="xl64" style="width: 54pt;" width="72">B</td> <td class="xl64" style="width: 54pt;" width="72">C</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">105.4</td> <td class="xl64">111.6</td> <td class="xl64">111.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">106.4</td> <td class="xl64">112.8</td> <td class="xl64">112.2</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">106.6</td> <td class="xl64">113</td> <td class="xl64">111.2</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">107.3</td> <td class="xl64">112.9</td> <td class="xl64">112.5</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">107.8</td> <td class="xl64">113.5</td> <td class="xl64">112.3</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">107.8</td> <td class="xl64">113.1</td> <td class="xl64">112.4</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">108.7</td> <td class="xl64">112.3</td> <td class="xl64">112.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">108.3</td> <td class="xl64">112.8</td> <td class="xl64">112.2</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">108.5</td> <td class="xl64">112.7</td> <td class="xl64">112</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">109.7</td> <td class="xl64">111.1</td> <td class="xl64">109.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl64" style="height: 11.25pt;" height="15">109.4</td> <td class="xl64">110.6</td> <td class="xl64">108.9</td> </tr> </tbody></table>
i want to insert B and C columns below column A. same this:
<table style="border-collapse: collapse; width: 54pt;" border="0" cellpadding="0" cellspacing="0" width="72"><col style="width: 54pt;" width="72"> <tbody><tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt; width: 54pt;" height="19" width="72">A</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">105.4</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">106.4</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">106.6</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">107.3</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">107.8</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">107.8</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">108.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">108.3</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">108.5</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">109.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">109.4</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">111.6</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.8</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">113</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.9</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">113.5</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">113.1</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.3</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.8</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">111.1</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">110.6</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">111.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.2</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">111.2</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.5</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.3</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.4</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112.2</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">112</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">109.7</td> </tr> <tr style="height: 14.25pt;" align="center" height="19"> <td style="height: 14.25pt;" height="19">108.9</td> </tr> </tbody></table>
how can i do that with a macro?

please note that this is a sample and actually i have 120 columns and want to change to one A column same this sample

thanks a lot

 

[VoG]

Try

Sub Concat()
Dim LC As Long, LR As Long, j As Long
LR = ActiveSheet.UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For j = 2 To LC
    Range(Cells(1, j), Cells(LR, j)).Cut Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
Next j
End Sub

 

[Peter_SSs]

I have assumed all columns have the same amount of data per your sample. Not sure if you wanted to also remove the original data from its location. Anyway, see it this is some help.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveCols()<br>    <SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    rws = Cells(Rows.Count, 1).End(xlUp).Row<br>    lc = Cells(1, Columns.Count).End(xlToLeft).Column<br>    nr = rws + 1<br>    <SPAN style="color:#00007F">For</SPAN> c = 2 <SPAN style="color:#00007F">To</SPAN> lc<br>        Cells(nr, 1).Resize(rws).Value = Cells(1, c).Resize(rws).Value<br>        nr = nr + rws<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[aminexcel]

Thanks a lot "VOG" and "Peter SSs"
both of them works

sincerely
amin