Changing a 4x1024 spreadsheet into a single column of 4096
Posted by Johnny-C on July 12, 2001 1:35 PM
I have data that are 4 columns wide and 1024 rows long. The data are read left to right, like a book, so that the first data point is A1, the second is B1, so the fifth would be A2, etc. I want to rearrange these data into a single column so that the data from A1 would go to F1, that from B1 would go in F2, that from C1 would go in F3, and so on. Is there a quick way to do this? Thanks.
Posted by Ben O. on July 12, 2001 2:16 PM
Johnny,
This macro should do the trick.
Sub RowsToColumns()
Application.ScreenUpdating = False
For myCRow = 1 To 1024
myPRow = myCRow * 4
Range(Cells(myCRow, 1), Cells(myCRow, 1).Offset(0, 3)).Select
Selection.Copy
Cells(myPRow, 6).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Next myCRow
Range("F1:F3").Delete Shift:=xlUp
End Sub
Posted by Ben O. on July 12, 2001 2:20 PM
A faster macro...
Not the the one I gave you takes long to run, but this one runs faster because it doesn't select each time it copies & pastes:
Sub RowsToColumns()
Application.ScreenUpdating = False
For myCRow = 1 To 1024
myPRow = myCRow * 4
Range(Cells(myCRow, 1), Cells(myCRow, 1).Offset(0, 3)).Copy
Cells(myPRow, 6).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Next myCRow
Range("F1:F3").Delete Shift:=xlUp
End Sub
Posted by Eric on July 12, 2001 2:24 PM
The code from a macro I recorded, and a basic description
Okay, don't get me wrong here- I do virtual basic script like monkeys do math, but in a sample set of data the same size as yours, this worked.
Assume your data starts in a1 on sheet 1.
Start recording a macro (I called mine "transfer1024"). Goto a1024, copy a1024:d1024, goto sheet 2, a1, paste special transpose paste your data, then insert 4 rows above the transpose pasted data in sheet 2. Goto cell a1 in sheet 2 (this resets sheet 2 for the pasting procedure). Then goto sheet 1, cell a1, insert row. Then go to sheet 1, cell a1024. stop recording. Go into macro, select your recently created macro, select edit, and add
"for m = 1 to 1024" to the top of the black text, then add "next m" to the bottom of the black text.
Takes about 2 min to run on my comp.
My code looks like this:
For m = 1 To 1024
Application.Goto Reference:="R1024C1"
Range("A1024:D1024").Select
Selection.Copy
ActiveSheet.Next.Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Selection.EntireRow.Insert
Range("A1").Select
ActiveSheet.Previous.Select
Range("A1024").Select
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
Selection.EntireRow.Insert
Application.Goto Reference:="R1024C1"
Next m
Hope that helps until someone more qualified comes along!
Posted by Johnny-C on July 12, 2001 2:29 PM
Re: A faster macro...
Wow--I'm impressed. What a great resource this board is. Ben, your help is much appreciated. It worked great.
