Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

Gord,

Thanks a lot for your reply! Now... this is a little much for me... I have
a few questions:

Where in VB editor do I paste this code?

When and how does printing occur and how do I tell the printer that the code
needs to be read and apply to the print job?

Is there also a way to repeat the first two rows at the top of every column
on the print job?

Hope this is not too much asking...

Feelu

"Gord Dibben" wrote:

There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

Gord,

I tried to modify your code so it woud be applicable for the general case
that we have a range i columns wide wich we want to "snake" to j colums,
where j is a multiple (k*i) of i (without having blank columns). But I
failed.

Please be so kind to provide the code. Thanks in advance.

Jack Sons
The Netherlands


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

Alt + F11 to open the VBEditor. CTRL + r to open the Project Explorer.

Right-click on your workbook/project and InsertModule.

Paste into that module.

The code only moves the 3 columns of data to 9 columns.

Printing and Print Setup is up to you.

Turn on the macro recorder when doing the print setup.


Gord

On Mon, 31 Mar 2008 06:28:01 -0700, FiluDlidu
wrote:

Gord,

Thanks a lot for your reply! Now... this is a little much for me... I have
a few questions:

Where in VB editor do I paste this code?

When and how does printing occur and how do I tell the printer that the code
needs to be read and apply to the print job?

Is there also a way to repeat the first two rows at the top of every column
on the print job?

Hope this is not too much asking...

Feelu

"Gord Dibben" wrote:

There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

Jack

Suggest you visit David McRitchie's site for his SnakeCols routine.

http://www.mvps.org/dmcritchie/excel/code/snakecol.txt


Gord

On Mon, 31 Mar 2008 16:06:06 +0200, "Jack Sons" wrote:

Gord,

I tried to modify your code so it woud be applicable for the general case
that we have a range i columns wide wich we want to "snake" to j colums,
where j is a multiple (k*i) of i (without having blank columns). But I
failed.

Please be so kind to provide the code. Thanks in advance.

Jack Sons
The Netherlands


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

Enormous! Thanks Gord.

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Jack

Suggest you visit David McRitchie's site for his SnakeCols routine.

http://www.mvps.org/dmcritchie/excel/code/snakecol.txt


Gord

On Mon, 31 Mar 2008 16:06:06 +0200, "Jack Sons" wrote:

Gord,

I tried to modify your code so it woud be applicable for the general case
that we have a range i columns wide wich we want to "snake" to j colums,
where j is a multiple (k*i) of i (without having blank columns). But I
failed.

Please be so kind to provide the code. Thanks in advance.

Jack Sons
The Netherlands


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
. ..
There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

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

On Sun, 30 Mar 2008 15:22:00 -0700, FiluDlidu
wrote:

Hi all,

Let's say I have long list of data in columns A, B and C, which in the
end
makes it a long skinny table. I would like a print to "wrap" the table
so
that it can go 3 or 4 times from the top to the bottom of the page
(we'll
assume it's a landscape printing or an 11x17 sheet...), without losing
the
continuity of the data and without having to physically move the
existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.