Hello!

I was wondering if anyone could help me; I have about 200 rows of data, with about 200 values in each row, but I would like to have the information that is in each row, to be in a column instead, just wondering is there a quick way of doing this as opposed to copying and pasting each of them?
All help appreciated!

thank you,

Kathryn

Have a look in the Excel Help for how to use "Paste Special / Transpose".
Should do exactly what you're after.

Hi Spencer,

Thank you that worked great!
Kathryn

On Sunday, July 15, 2012 9:09:40 AM UTC-5, Kathryn39 wrote:
Hello!

I was wondering if anyone could help me; I have about 200 rows of data,
with about 200 values in each row, but I would like to have the
information that is in each row, to be in a column instead, just
wondering is there a quick way of doing this as opposed to copying and
pasting each of them?
All help appreciated!

thank you,

Kathryn




--
Kathryn39

This should do it

Sub transposeemSAS()
Dim lr As Long
Dim i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
Rows(i).Copy
Cells(Rows.Count, i).End(xlUp)(2).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, transpose:=True
'MsgBox i
Next i
Rows(1).Resize(lr).Delete
End Sub

Pleasure was all mine, Kathryn. Glad to be of help. :)

Definitively you can copy from row and paste special to column but there are other methods around.

You can try recording a micro to do your job. On the other hand you can use OFFSET formula to do your job.

You can also visit http://www.techyv.com/questions/simp...ow-data-column for more information.

I hope this would be of some help.