Need Transpose Help and Removing Blank Cell

[fahadmalik09]

I want to add Rows into Column

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Style[/TD]
[TD]style1[/TD]
[TD]Style 2[/TD]
[TD]Style 3[/TD]
[TD]Style 4[/TD]
[TD]Style 5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12A[/TD]
[TD]12B[/TD]
[TD]12C[/TD]
[TD]12D[/TD]
[TD]12E[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]13A[/TD]
[TD]13B[/TD]
[TD]13C[/TD]
[TD]13D[/TD]
[TD]13E[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]14A[/TD]
[TD]14B[/TD]
[TD]14C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15A[/TD]
[TD]15B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


To this one given below

[TABLE="width: 500"]
<tbody>[TR]
[TD]Style [/TD]
[TD]Style 1[/TD]
[TD]Style 2[/TD]
[TD]Style 3[/TD]
[TD]Style 4[/TD]
[TD]Style 5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12A[/TD]
[TD]12B[/TD]
[TD]12C[/TD]
[TD]12D[/TD]
[TD]12E[/TD]
[/TR]
[TR]
[TD]12A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]13A[/TD]
[TD]13B[/TD]
[TD]13C[/TD]
[TD]13D[/TD]
[TD]13E[/TD]
[/TR]
[TR]
[TD]13A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]14A[/TD]
[TD]14B[/TD]
[TD]14C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15A[/TD]
[TD]15B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ooops...i made a long table ....

Anyways... i want to transpose Rows into column of that particular row, so that each cell will be under that Column .

Some blank cell are also given, like 15 entry...so want to remove that entry....


Can it be done around 20k entries are there...


Thanks in advance.....

 

[MickG]

Try this:-
This code will over write your Data starting in "A2" !!!

I'm not sure what this means, Please show example

Some blank cell are also given, like 15 entry...so want to remove that entry.

[COLOR=Navy]Sub[/COLOR] MG27Dec05
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, sLst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
sLst = Cells("1", Columns.Count).End(xlToLeft).Column
ReDim ray(1 To Rng.Count * sLst, 1 To sLst)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
        c = c + 1
        [COLOR=Navy]For[/COLOR] n = 1 To Lst
            ray(c, n) = Dn.Offset(, n - 1)
        [COLOR=Navy]Next[/COLOR] n
        [COLOR=Navy]For[/COLOR] n = 1 To Lst - 1
            c = c + 1
            ray(c, 1) = Dn.Offset(, n)
        [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] Dn
Range("A2").Resize(c, sLst).Value = ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[fahadmalik09]

"Some blank cell are also given, like 15 entry...so want to remove that entry...."

The column in the above example with "Style" data "15"

i am using Excel 2010 so dont know how VBA will work. NewBie on Excel

And thanks Mick for your kind help, but dont know where to put this code?? Can you please elaborate more....

 

[MickG]

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

 

[fahadmalik09]

It works..and it overwrite the A2 cell...
But it converted all the sheet.....and your help is sufficient what i asked....

But i forgot to mention that i dont want to convert all sheet....
There is several Rows say example.... A B C D E F G H I....... with column 1 2 3 4 5 6

I need E Row and GHI Rows and want GHI cell vaules under E row....

I need E1 cell and G1 H1 I1
E
1. E1
2. G1
3. H1
4. I1

Down know how to express it....

Want to add data on E row with G H I Row


Thanks for explaining BA too...Your code work fine but it convert the whole sheet, i want to convert Some Rows data to particular Row

Thanks Mike