Xpose twenty rows then the next twent etc.
Hi Howrd,
Am Wed, 4 Sep 2013 21:39:35 -0700 (PDT) schrieb Howard:
10 to 109 - 5 rows ok and 1 row with 19 commas
100 to 199 - 5 rows ok and 5 rows of 19 commas
a1 to a100 Type mismatch error.
try:
Sub SuperJoin()
Dim i As Long, x As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20
x = x + 1
With WorksheetFunction
Range("B" & x) = Join(Application.Transpose(Range(Cells(i, 1), _
Cells(i + .Min(19, .CountA(Range(Cells(i, 1), _
Cells(i + 19, 1))) - 1), 1))), ",")
End With
Next
End Sub
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|