List output to a x by 10 range

  • Thread starter Thread starter willwonka
  • Start date Start date
W

willwonka

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?
 
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/archives/2006/12/04/writing-to-a-range-using-vba
 
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.
 
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" <[email protected]>
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.
 
Back
Top