Xpose twenty rows then the next twent etc.
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.
Hi Claus,
With:
10 to 109 - works fine.
a1 to a100 - works fine.
100 strings like these two - works fine.
MAE511363
XEL551995
With this
100 to 199 I get five Scientific Notations like this 1.00101102103104E+59
I be way stumped. I checked column B cell format and nothing is selected.
I tried this at the end
Columns("B:B").Select
Selection.NumberFormat = "General"
and it did not fix anything.
Regards,
Howard
|