Hi Howard,

Am Mon, 15 Jun 2015 17:11:05 -0700 (PDT) schrieb L. Howard:

Column A has a LONG (2000- 4000 + rows) in set of 9 row "clumps" of data.

11 to 19
21 to 29
31 to 39
etc
etc

How do I set myRng to each 9 row clump to be transposed to D2 to L2 and on down, (which works fine using the last line of code.)

or without array:

Sub Transpose()
Dim LRow As Long, i As Long, n As Long
Dim myRng As Range

n = 2
LRow = Cells(Rows.Count, 1).End(xlUp).Row
With Application
.ScreenUpdating = False
For i = 11 To LRow Step 10
Set myRng = Range(Cells(i, 1), Cells(i + 8, 1))
If .CountA(myRng) 0 Then
Range("D" & n).Resize(1, 9) = .Transpose(myRng)
n = n + 1
End If
Next
.ScreenUpdating = True
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional