List output to a x by 10 range
 

I have a list that x lines long. I want to transpose that list to
another range (starting on cell M1) that is 10 columns wide and
however many rows long.

I know this is super simple.

Does it have something to do with range resizing?

List output to a x by 10 range
 

No doubt you've already experimented with macro recorder, discovering
the standard transpose command wont wrap within a range. So we need
handle the transposition manually.
Presuming your data begins in cell A2:

Sub test()
For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row)
Row = Row + 1
For col = 0 To 9
Cells(Row, 13 + col).Value = c.Value
Next col
Next c
End Sub

Note however this method will take awhile if you have a lot of data -
in which case I suggest considering John Walkenbach's array method.
http://www.dailydoseofexcel.com/arch...ange-using-vba

List output to a x by 10 range
 

Thanks. Unfortunately, that just copied the list to 10 columns (which
may be what I asked given the way I worded it).

Let's try this.

Let's say I have a range (we'll call it "data"). This Range is from
A2:A1770 (so it is 1769 rows long).

I would like to transpose that range where it would be 177 (1769 /
10+1) rows by 10 columns starting in Cell M1.

In other words, it would take the first 10 rows of "data" and place
them in cells M1:V1
The next 10 rows would be placed in M2:V2

I appreciate the link to JWalk's site. He is always helpful and I
think I would definitely like to use arrays for this.

So reading the range into an array and then perhaps resize the array
( making it 1769x1 to 177x10) and then just paste that resized array
to the right place.


Thanks, in advance for your help.






On Feb 17, 10:54*am, Jef Gorbach wrote:
No doubt you've already experimented with macro recorder, discovering
the standard transpose command wont wrap within a range. So we need
handle the transposition manually.
Presuming your data begins in cell A2:

Sub test()
For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row)
* * Row = Row + 1
* * For col = 0 To 9
* * * * Cells(Row, 13 + col).Value = c.Value
* * Next col
Next c
End Sub

Note however this method will take awhile if you have a lot of data -
in which case I suggest considering John Walkenbach's array method.http://www.dailydoseofexcel.com/arch...ting-to-a-rang...

List output to a x by 10 range
 

You just needed to adjust the code Jef wrote so that the outside loop skipped every ten rows - see "step 10" below...
'--
Sub TransposeTest()
Dim c As Long
Dim rngData As Range
Dim lngRow As Long
Dim lngCol As Long

Set rngData = Range("A2", Cells(Rows.Count, 1).End(xlUp))
For c = 1 To rngData.Rows.Count Step 10
lngRow = lngRow + 1
For lngCol = 0 To 9
rngData(lngRow, 13 + lngCol).Value = rngData(c + lngCol, 1).Value
Next
Next c
End Sub
'--
Jim Cone
Portland, Oregon USA
(free Excel downloads at http://excelusergroup.org/)




"willwonka"
wrote in message ...
Thanks. Unfortunately, that just copied the list to 10 columns (which
may be what I asked given the way I worded it).

Let's try this.

Let's say I have a range (we'll call it "data"). This Range is from
A2:A1770 (so it is 1769 rows long).

I would like to transpose that range where it would be 177 (1769 /
10+1) rows by 10 columns starting in Cell M1.

In other words, it would take the first 10 rows of "data" and place
them in cells M1:V1
The next 10 rows would be placed in M2:V2

I appreciate the link to JWalk's site. He is always helpful and I
think I would definitely like to use arrays for this.

So reading the range into an array and then perhaps resize the array
( making it 1769x1 to 177x10) and then just paste that resized array
to the right place.
Thanks, in advance for your help.

List output to a x by 10 range
 

Thanks this did the trick and I can work with it.


On Feb 18, 8:35*pm, "Jim Cone" wrote:
You just needed to adjust the code Jef wrote so that the outside loop skipped every ten rows - see "step 10" below...
'--
Sub TransposeTest()
Dim c As Long
Dim rngData As Range
Dim lngRow As Long
Dim lngCol As Long

Set rngData = Range("A2", Cells(Rows.Count, 1).End(xlUp))
For c = 1 To rngData.Rows.Count Step 10
* * lngRow = lngRow + 1
* * For lngCol = 0 To 9
* * * * rngData(lngRow, 13 + lngCol).Value = rngData(c + lngCol, 1).Value
* * Next
Next c
End Sub
'--
Jim Cone
Portland, Oregon *USA
(free Excel downloads at *http://excelusergroup.org/)

"willwonka"
wrote in ...
Thanks. *Unfortunately, that just copied the list to 10 columns (which
may be what I asked given the way I worded it).

Let's try this.

Let's say I have a range (we'll call it "data"). *This Range is from
A2:A1770 (so it is 1769 rows long).

I would like to transpose that range where it would be 177 (1769 /
10+1) rows by 10 columns starting in Cell M1.

In other words, it would take the first 10 rows of "data" and place
them in cells M1:V1
The next 10 rows would be placed in M2:V2

I appreciate the link to JWalk's site. *He is always helpful and I
think I would definitely like to use arrays for this.

So reading the range into an array and then perhaps resize the array
( making it 1769x1 to 177x10) and then just paste that resized array
to the right place.
Thanks, in advance for your help.