View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Tom Hutchins Tom Hutchins is offline
external usenet poster
 
Posts: 1,069
Default Transposing columns to rows

Try this macro. It assumes the data in column A is in contiguous rows. If
it's not, let me know (via this thread) and I will send a revised version.

Sub Macro1()
Dim CurrRow As Long
CurrRow = 1
Cells(CurrRow, 1).Activate
Do While Len(ActiveCell.Value) 0
Range("A" & CurrRow + 1 & ":A" & CurrRow + 8).Select
Selection.EntireRow.Insert
Range("B" & CurrRow & ":I" & CurrRow).Select
Selection.Copy
Range("A" & CurrRow + 1 & ":A" & CurrRow + 8).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Cells(CurrRow + 9, 1).Activate
CurrRow = ActiveCell.Row
Loop
'Delete columns B-I
Columns("B:I").Select
Selection.Delete Shift:=xlToLeft
End Sub

Paste this code in a VBA module in your workbook. If you're new to macros,
you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

"MosheMo" wrote:

Hello,

I am asking this question on behalf of my father. He has a spreadsheet of
data which is about 2000 rows long and 9 columns wide. My dad wants to
transpose the data in columns B - I in the following manner:

The data in B1 will move into cell A2 (and the data in A2 will move down to
A3). The data in C1 will move into cell A3 (and the data in A3 will move
down to A4).
And so on until I1 becomes A9 and the data in A9 moves over to A10.
This happens row after row - i.e., B2 becomes A11; C2 A12, etc. for all
2,000 rows.

Any ideas how this can be down?

Thanks and be well,

Moshe