Xpose twenty rows then the next twent etc.

[Howard]

Want to xpose a list of items in A to cells in B twenty at a time then the next twentyin B below the first etc.

Thanks,
Howard

Option Explicit

Sub SuperJoin()
Range("B1") = Join(Application.Transpose(Range(Range("A1"), _
Range("A" & Rows.Count).End(xlUp))), " , ") '" / ")
End Sub

 

[isabelle]

hi Howard,

Sub SuperJoin()
Dim i As Long, x As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20
x = x + 1
Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i),
Range("A" & i + 19))), ",")
Next
End Sub

isabelle

Le 2013-09-04 22:22, Howard a écrit :

 

[isabelle]

sorry it was missing a space at the last argument of Join function

Sub SuperJoin()
Dim i As Long, x As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20
x = x + 1
Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i),
Range("A" & i + 19))), " ,")
Next
End Sub

isabelle


Le 2013-09-04 23:28, isabelle a écrit :

 

[Howard]

sorry it was missing a space at the last argument of Join function



Sub SuperJoin()

Dim i As Long, x As Long

For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20

x = x + 1

Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i),

Range("A" & i + 19))), " ,")

Next

End Sub



isabelle

Very nice. Thank you isabelle.

Regards,
Howard

 

[Howard]

Hi isabelle,

My first response was to a test of 1 to 100 in col A.

Worked just fine, 1 to 100 - 5 rows, 1-20, 21-40, 41-60, 61-80, 81-100.

I did some more tests with these results which puzzle me.

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.

Regards,
Howard

 

[Claus Busch]

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.

 

[Howard]

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

 

[Claus Busch]

Hi Howard,

Am Wed, 4 Sep 2013 23:00:52 -0700 (PDT) schrieb Howard:

100 strings like these two - works fine.
MAE511363
XEL551995

With this
100 to 199 I get five Scientific Notations like this 1.00101102103104E+59

first, you don't need the MIN
The comma is the separator for the thousands and so you will get a very
great number. Try semicolon as separator if you have numbers in your
range:

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 + .CountA( _
Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ";")
End With
Next
End Sub

or comma with a following space:

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 + .CountA( _
Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ", ")
End With
Next
End Sub


Regards
Claus B.

 

[Howard]

first, you don't need the MIN

The comma is the separator for the thousands and so you will get a very

great number. Try semicolon as separator if you have numbers in your

range:



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 + .CountA( _

Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ";")

End With

Next

End Sub



or comma with a following space:



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 + .CountA( _

Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ", ")

End With

Next

End Sub





Regards

Claus B.

Thanks Claus, that cleared it up very nicely. All works excellent.

Regards,
Howard