Data from row to Column

[amna77]

Hi There,
I am having trouble with row and columns.
Actually I have data in one row.

right now I have one row and 4 columns
Row1= A B C D

now I want to Take this data from this row and paste it under one column on different sheet, but on 4 rows.
A
B
C
D

and rows can grow, some times, data is going to be only in 2 rows, and some times it could be 20 rows.
So can you please tell me a way how to find out the last row. One you run the macro, it should always look all rows with data and put them in column.
Like data from row 1, should go to column 1 in next sheet.
and row 2 data should go to under column 2 in next sheet and so on.
Please help me out here. I shall be thankful to you.
Thanks





[ This Message was edited by: amna77 on 2002-05-10 14:38 ]

[bergy]

Are you looking to just find the last row? If so, check out the following post from earlier today:
http://www.mrexcel.com/board/viewtop...7987&forum=2&2

If you need to know how to transpose your data, look under PasteSpecial and check Transpose. YOu could record the macro and make the adjustments in the code from the post above.

[NateO]

Also, you'll want an error trapper if you have more than 256 rows of data. Excel only has 256 columns, transposing 300 rows will indeed make excel crash. Something like the following:

Sub test()
Dim i As Long
i = Sheet1.[a65536].End(xlUp).Row 'test column A
If i > 256 Then
MsgBox ("You have more rows than available columns, XL will crash." _
    & Chr(13) & Chr(13) & "Task Aborted.")
End
End If
Sheet1.Range("a1:d" & i).Copy '4 columns wide a:d (change if necessary)
Sheet2.[a1].PasteSpecial Paste:=xlAll, Transpose:=True
Sheet2.[a1].Select 'only because transposing selects without our permission
Application.CutCopyMode = False
End Sub

Change sheet1 & sheet2 to sheets("sheet name"), etc...

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-10 15:01 ]