Scroll data into 3 columns

  • Thread starter Thread starter Brenda from Michigan
  • Start date Start date
B

Brenda from Michigan

Excel 2003 SP3

User has 3-columns of data with hundreds of records. She wants to take
these 3-columns and scroll them, in order, into 9-columns on her Excel page.
Think of Word wrapping it's columns while maintaining the order of the data.
Is this possible to do in Excel, and if so, how? Thank you.
 
Try this to snake 3 columns into 9

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


Gord Dibben MS Excel MVP
 
Back
Top