Easily done using VBA code.
Public Sub Snake3to9()
Dim myRange As Range
Dim colsize As Long
Dim maxrow As Long
Const numgroup As Integer = 3
Const NUMCOLS As Integer = 9
On Error GoTo fileerror
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
((NUMCOLS - 1)) / NUMCOLS)) / numgroup
MsgBox "Number of Rows to Move is: " & colsize
Range("A1").Select
With ActiveCell.Parent.UsedRange
maxrow = .Cells(.Cells.Count).Row + 1
End With
ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _
.End(xlUp).Offset(1, 0).Select
Set myRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 1)).Address)
myRange.Cut Destination:=ActiveSheet.Range("A1") _
.Offset(0, (NUMCOLS - numgroup))
Range("A1").Select
Cells.End(xlDown).Offset(1, 0).Select
Set NextRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 1)).Address)
NextRange.Cut Destination:=ActiveSheet.Range("A1") _
.Offset(0, (NUMCOLS / numgroup))
Application.CutCopyMode = False
Range("A1").Select
fileerror:
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord Dibben MS Excel MVP
