ExcelBanter - TRANSPOSING

Hi Gary,

Try this macro solution (from your example I assumed that row 5 is missing
from the 7 row blocks and you copy from "Sheet1" to "Sheet2"):

Sub trpose()
headrow = 1
lastrow = 0
blockcounter = 0
Do While blocklength(headrow, lastrow) 0
blockcounter = blockcounter + 1
If lastrow - headrow = 6 Then
Range("A" & headrow + 4).Select
Selection.EntireRow.Insert
lastrow = lastrow + 1
End If
' MsgBox (headrow & "-" & lastrow)
Range("A" & headrow & ":A" & lastrow).Select
Selection.Copy
Worksheets("Sheet2").Activate
Range("A" & blockcounter).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Worksheets("Sheet1").Activate
headrow = lastrow + 1
Loop
Application.CutCopyMode = False
End Sub
Function headid(headcell)
headid = Left(headcell, 1) = "0" _
And Mid(headcell, 2, 3) = " " _
And IsNumeric(Mid(headcell, 5, 4)) _
And Mid(headcell, 9, 1) = "-" _
And IsNumeric(Mid(headcell, 10, 4))
End Function
Function blocklength(thisheadrow, lastblockrow)
notheadrows = thisheadrow + 7
Do Until headid(Range("A" & notheadrows)) Or IsEmpty(Range("A" &
notheadrows))
notheadrows = notheadrows + 1
Loop
lastblockrow = notheadrows - 1
nextheadrow = notheadrows
blocklength = IIf(IsEmpty(Range("A" & thisheadrow)), 0, nextheadrow -
thisheadrow)
End Function

Sorry if testing is not perfect!

Regards,
Stefi

€žGARY€ ezt Ã*rta:

Column A contains 'blocks' of either 7 or 8 cells. For example:

0CLOTHESTIME STORE INC

C/O GENERAL ACCTG

5325 E HUNTER AVE 03/15/2006

ANAHEIM CA 92807

215.99
217.99
0 200301234 2003-2004 004-018
000027991-8
0
2003-2004
0CROCKER, JAMES R & BARBARA J

41398 SHADOW MOUNTAIN WY

HEMET CA 92544 03/15/2006

51.45
51.95
0 200301244 2003-2004 006-165
000028278-1
0
2004-2005

The first cell in each 'block' contains, in the two leftmost positions,
a '0' (zero) and a non-space character. For example:

The first cell of the first 'block' contains: 0CLOTHESTIME STORE
INC
The first cell of the second 'block' contains: 0CROCKER, JAMES R &
BARBARA J

I need to tranpose the cells of each block. But how do I accomodate
the varying number of cells in each 'block'?