I am looking for a macro that will transpose a long single column o
data (each record is seperated by a blank cell in the column) int
seperate rows (each row representing a speperate record) This data se
only has nine fields per record, no formulas. I dont want to do thi
by hand.

Thanks

--
unknowndevic
-----------------------------------------------------------------------
unknowndevice's Profile: http://www.excelforum.com/member.php...fo&userid=2664
View this thread: http://www.excelforum.com/showthread.php?threadid=39949

Assuming source data is in column A and data is to be transposed to columns
B to K (including the blank cell)

Sub TransposeData()
sr = 1
dr = 0
For sr = 1 To 100 Step 10 ' Change 100 to suit number of rows in the source
column
dr = dr + 1
Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A10 first
loop, then A11:A20 next loop etc
Selection.Copy
Cells(dr, 2).Select ' Select B1 first loop, then B2 second loop etc
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next
End Sub

--
Ian
--
"unknowndevice"
wrote in message
news:unknowndevice.1udpqm_1125072336.1956@excelfor um-nospam.com...

I am looking for a macro that will transpose a long single column of
data (each record is seperated by a blank cell in the column) into
seperate rows (each row representing a speperate record) This data set
only has nine fields per record, no formulas. I dont want to do this
by hand.

Thanks!


--
unknowndevice
------------------------------------------------------------------------
unknowndevice's Profile:
http://www.excelforum.com/member.php...o&userid=26646
View this thread: http://www.excelforum.com/showthread...hreadid=399495

unknowndevice wrote:
I am looking for a macro that will transpose a long single column of
data (each record is seperated by a blank cell in the column) into
seperate rows (each row representing a speperate record) This data set
only has nine fields per record, no formulas. I dont want to do this
by hand.

Thanks!



Asuming your data is in A1:A1000, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your
workbook you can array enter into B1:K100

=ArrayReshape(A1:A1000,100,10)

Alan Beban

You have other replies to your post in .misc.

unknowndevice wrote:

I am looking for a macro that will transpose a long single column of
data (each record is seperated by a blank cell in the column) into
seperate rows (each row representing a speperate record) This data set
only has nine fields per record, no formulas. I dont want to do this
by hand.

Thanks!

--
unknowndevice
------------------------------------------------------------------------
unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646
View this thread: http://www.excelforum.com/showthread...hreadid=399495

--

Dave Peterson