Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cutting and pasting ranges using offsets
Can any one help me? I have a spreadsheet with the following data which I'm
trying to format into one group of 4 columns. Basically, I want a macro to select and cut every 4 columns of data (from left to right) and paste them all in columns A:D. My goal is to turn the horizontal data into more readible verticle data. I know my description might not make sense to you, but if you look data below you might be able to understand what I'm trying to accomplish. I will need to use some sort of loop code to perform my task b/c the verticle data in my spreadsheet is much larger than the sample I provided here. data currently in spreadsheet: 52200 R0646 54000 17625.9 52200 R0646 54001 0 52201 R0646 54000 0 52201 R0646 54001 0 52203 R0646 54000 0 52203 R0646 54001 0 52204 R0646 54000 10508.34 52204 R0646 54001 3950 52206 R0646 54000 0 52206 R0646 54001 0 Format I need: 52200 R0646 54000 17625.9 52201 R0646 54000 0 52203 R0646 54000 0 52204 R0646 54000 10508.34 52206 R0646 54000 0 52200 R0646 54001 0 52201 R0646 54001 0 52203 R0646 54001 0 52204 R0646 54001 3950 52206 R0646 54001 0 Any help would be appreciated. -- Regards, timmulla |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cutting and pasting ranges using offsets
Tim,
Assuming your data table starts in cell A1: Sub TryNow() Dim i As Integer Dim myRange As Range Set myRange = Range("A1").CurrentRegion For i = 5 To myRange.Columns.Count Step 4 myRange.Columns(i).Resize(, 4).Cut _ Cells(65536, myRange.Cells(1).Column).End(xlUp)(2) Next i End Sub HTH, Bernie MS Excel MVP "timmulla" wrote in message ... Can any one help me? I have a spreadsheet with the following data which I'm trying to format into one group of 4 columns. Basically, I want a macro to select and cut every 4 columns of data (from left to right) and paste them all in columns A:D. My goal is to turn the horizontal data into more readible verticle data. I know my description might not make sense to you, but if you look data below you might be able to understand what I'm trying to accomplish. I will need to use some sort of loop code to perform my task b/c the verticle data in my spreadsheet is much larger than the sample I provided here. data currently in spreadsheet: 52200 R0646 54000 17625.9 52200 R0646 54001 0 52201 R0646 54000 0 52201 R0646 54001 0 52203 R0646 54000 0 52203 R0646 54001 0 52204 R0646 54000 10508.34 52204 R0646 54001 3950 52206 R0646 54000 0 52206 R0646 54001 0 Format I need: 52200 R0646 54000 17625.9 52201 R0646 54000 0 52203 R0646 54000 0 52204 R0646 54000 10508.34 52206 R0646 54000 0 52200 R0646 54001 0 52201 R0646 54001 0 52203 R0646 54001 0 52204 R0646 54001 3950 52206 R0646 54001 0 Any help would be appreciated. -- Regards, timmulla |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cutting and pasting ranges using offsets
This macro does what I think you want. Watch out for line wrapping in this
message. Expand this message to see the code properly. HTH Otto Sub MoveData() Dim c As Long For c = 5 To 62 Step 4 If Cells(1, c) = "" Then Exit For Range(Cells(1, c), Cells(65536, c).End(xlUp).Offset(, 3)).Copy _ Cells(65536, 1).End(xlUp).Offset(1) Next c End Sub "timmulla" wrote in message ... Can any one help me? I have a spreadsheet with the following data which I'm trying to format into one group of 4 columns. Basically, I want a macro to select and cut every 4 columns of data (from left to right) and paste them all in columns A:D. My goal is to turn the horizontal data into more readible verticle data. I know my description might not make sense to you, but if you look data below you might be able to understand what I'm trying to accomplish. I will need to use some sort of loop code to perform my task b/c the verticle data in my spreadsheet is much larger than the sample I provided here. data currently in spreadsheet: 52200 R0646 54000 17625.9 52200 R0646 54001 0 52201 R0646 54000 0 52201 R0646 54001 0 52203 R0646 54000 0 52203 R0646 54001 0 52204 R0646 54000 10508.34 52204 R0646 54001 3950 52206 R0646 54000 0 52206 R0646 54001 0 Format I need: 52200 R0646 54000 17625.9 52201 R0646 54000 0 52203 R0646 54000 0 52204 R0646 54000 10508.34 52206 R0646 54000 0 52200 R0646 54001 0 52201 R0646 54001 0 52203 R0646 54001 0 52204 R0646 54001 3950 52206 R0646 54001 0 Any help would be appreciated. -- Regards, timmulla |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Aligning with out cutting and pasting | Excel Worksheet Functions | |||
Cutting & pasting from one workbook to another | Excel Discussion (Misc queries) | |||
Cutting and Pasting with spaces | Excel Discussion (Misc queries) | |||
Cutting and Pasting in Excel | Excel Discussion (Misc queries) | |||
Formats when cutting and pasting | Excel Discussion (Misc queries) |