cutting and pasting ranges using offsets

[Guest]

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. 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.

 

[Bernie Deitrick]

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

 

[Otto Moehrbach]

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