Need Transpose Help and Removing Blank Cell

[fahadmalik09]

I want to add Rows into Column

Example:

Style	style1	Style 2	Style 3	Style 4	Style 5
12	12A	12B	12C	12D	12E
13	13A	13B	13C	13D	13E
14	14A	14B	14C
15	15A	15B
16	16A

<tbody>
</tbody>

To this one given below

Style	Style 1	Style 2	Style 3	Style 4	Style 5
12	12A	12B	12C	12D	12E
12A
12B
12C
12D
12E
13	13A	13B	13C	13D	13E
13A
13B
13C
13D
13E
14	14A	14B	14C
14A
14B
14C
15	15A	15B
15A
15B
16	16A
16A

<tbody>
</tbody>

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