Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Way to output list of macros to file | Excel Programming | |||
Compare 2 Columns, then output a list | Excel Programming | |||
Output list based on value in a cell | Excel Programming | |||
daily output list of employees | Excel Worksheet Functions | |||
Ooh .. Linking a list to a list to an output cell | Excel Discussion (Misc queries) |