Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuous conversion of data from rows to columns
I have roows of data looks that look like this:
Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3 ...... I'm trying to convert the above to look like this: Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3..... I created a macro within Excel to arrange the Text1 data, but how can I continue reading and arranging the next rows within the macro to repeat what was done to the first one? I assume it's a Do Loop, but just need the code to force it to read Row2 and repeat the same process for an "n" number of rows. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuous conversion of data from rows to columns
Now I see your problem from yesterday... Here is some code that I think will
work for you... Public Sub ConsolidateRows() Dim wksCopyFrom As Worksheet Dim rngCopyFrom As Range Dim wksCopyTo As Worksheet Dim rngCopyTo As Range Set wksCopyFrom = ActiveSheet Set rngCopyFrom = wksCopyFrom.Range("A2") Set wksCopyTo = Worksheets.Add Set rngCopyTo = wksCopyTo.Range("A2") Do While rngCopyFrom.Value < "" Range(rngCopyFrom, rngCopyFrom.Offset(0, 7)).Copy rngCopyTo Set rngCopyTo = rngCopyTo.Offset(1, 0) Range(rngCopyFrom.Offset(0, 8), rngCopyFrom.Offset(0, 11)).Copy rngCopyTo.Offset(0, 4) Set rngCopyTo = rngCopyTo.Offset(1, 0) Range(rngCopyFrom.Offset(0, 12), rngCopyFrom.Offset(0, 15)).Copy rngCopyTo.Offset(0, 4) Set rngCopyTo = rngCopyTo.Offset(1, 0) Set rngCopyFrom = rngCopyFrom.Offset(1, 0) Loop End Sub It Creates a sheet and copies the data over as you have indicated in your question. It does however assume that your data will be exactly as you indicated in your question with 4 text and 11 numbers. I hope that will be OK. I trust that this is a little better than my ramblings of yesterday. HTH "Karaman" wrote: I have roows of data looks that look like this: Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3 ...... I'm trying to convert the above to look like this: Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3..... I created a macro within Excel to arrange the Text1 data, but how can I continue reading and arranging the next rows within the macro to repeat what was done to the first one? I assume it's a Do Loop, but just need the code to force it to read Row2 and repeat the same process for an "n" number of rows. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuous conversion of data from rows to columns
Jim,
Thank you very help for your help. I really appreciate the effort. Now I can process a lot of data easily. Best regards, Karaman "Jim Thomlinson" wrote: Now I see your problem from yesterday... Here is some code that I think will work for you... Public Sub ConsolidateRows() Dim wksCopyFrom As Worksheet Dim rngCopyFrom As Range Dim wksCopyTo As Worksheet Dim rngCopyTo As Range Set wksCopyFrom = ActiveSheet Set rngCopyFrom = wksCopyFrom.Range("A2") Set wksCopyTo = Worksheets.Add Set rngCopyTo = wksCopyTo.Range("A2") Do While rngCopyFrom.Value < "" Range(rngCopyFrom, rngCopyFrom.Offset(0, 7)).Copy rngCopyTo Set rngCopyTo = rngCopyTo.Offset(1, 0) Range(rngCopyFrom.Offset(0, 8), rngCopyFrom.Offset(0, 11)).Copy rngCopyTo.Offset(0, 4) Set rngCopyTo = rngCopyTo.Offset(1, 0) Range(rngCopyFrom.Offset(0, 12), rngCopyFrom.Offset(0, 15)).Copy rngCopyTo.Offset(0, 4) Set rngCopyTo = rngCopyTo.Offset(1, 0) Set rngCopyFrom = rngCopyFrom.Offset(1, 0) Loop End Sub It Creates a sheet and copies the data over as you have indicated in your question. It does however assume that your data will be exactly as you indicated in your question with 4 text and 11 numbers. I hope that will be OK. I trust that this is a little better than my ramblings of yesterday. HTH "Karaman" wrote: I have roows of data looks that look like this: Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3 ...... I'm trying to convert the above to look like this: Text1 Text1 Text1 Text1 1 2 3 4 5 6 7 8 9 10 11 Text2 Text2 Text2 Text2 1 2 3 4 5 6 7 8 9 10 11 Text3..... I created a macro within Excel to arrange the Text1 data, but how can I continue reading and arranging the next rows within the macro to repeat what was done to the first one? I assume it's a Do Loop, but just need the code to force it to read Row2 and repeat the same process for an "n" number of rows. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeated conversion of data summarized into rows and columns | Excel Discussion (Misc queries) | |||
Transpose columns into continuous rows | Excel Worksheet Functions | |||
Data Conversion - times, dates & rows | Excel Worksheet Functions | |||
Turning rows of equal number of columns into 1 continuous row | Excel Worksheet Functions | |||
Continued Conversion of data from rows to columns in a macro. | Excel Programming |