How to "merge" multiple colums to reduce page count
 

Sorry for the cryptic subject - wasn't sure how to explain.

I have a spreadsheet comprising (3) columns and 41,000+ lines.

I need to know if there is a easy way to reduce the page count vs cutting
and pasting additional columns to the right.

Any thoughts would be appreciated.

Jon

How to "merge" multiple colums to reduce page count
 

So you want to print let say 9 columns to a page. Assuming that to be correct
then there is nothing as direct as you would want. I would be inclined to on
a seperate tab just use formulas to transfer the data over.

in A1 =Sheet1!A1
in B1 =Sheet1!B1
in C1 =Sheet1!C1
in D1 =Sheet1!A14000
in E1 =Sheet1!B14000
in F1 =Sheet1!C14000
in G1 =Sheet1!A28000
in H1 =Sheet1!B28000
in I1 =Sheet1!C28000

and drag down. If this is a spreadsheet that you use a lot these formulas
will cause your spreadsheet to slow down as you will reach the calculation
limit of xl and any calculation will cuase a full workbook recalc. You might
want to delete the formulas after you print or switch calculation to manual.



--
HTH...

Jim Thomlinson


"jmcclain" wrote:

Sorry for the cryptic subject - wasn't sure how to explain.

I have a spreadsheet comprising (3) columns and 41,000+ lines.

I need to know if there is a easy way to reduce the page count vs cutting
and pasting additional columns to the right.

Any thoughts would be appreciated.

Jon

How to "merge" multiple colums to reduce page count
 

Jim,

Can you explain a bit more? Do i need to create a new tab and enter the
formulas just as you noted? It's a bit out of my comfort level.

I added the first formula, and I get a dialog box for "update values?

Can you elaborate?



"Jim Thomlinson" wrote:

So you want to print let say 9 columns to a page. Assuming that to be correct
then there is nothing as direct as you would want. I would be inclined to on
a seperate tab just use formulas to transfer the data over.

in A1 =Sheet1!A1
in B1 =Sheet1!B1
in C1 =Sheet1!C1
in D1 =Sheet1!A14000
in E1 =Sheet1!B14000
in F1 =Sheet1!C14000
in G1 =Sheet1!A28000
in H1 =Sheet1!B28000
in I1 =Sheet1!C28000

and drag down. If this is a spreadsheet that you use a lot these formulas
will cause your spreadsheet to slow down as you will reach the calculation
limit of xl and any calculation will cuase a full workbook recalc. You might
want to delete the formulas after you print or switch calculation to manual.



--
HTH...

Jim Thomlinson


"jmcclain" wrote:

Sorry for the cryptic subject - wasn't sure how to explain.

I have a spreadsheet comprising (3) columns and 41,000+ lines.

I need to know if there is a easy way to reduce the page count vs cutting
and pasting additional columns to the right.

Any thoughts would be appreciated.

Jon

How to "merge" multiple colums to reduce page count
 

Yes you would want to create a new worksheet and add the formula to that new
worksheet.

--
HTH...

Jim Thomlinson


"jmcclain" wrote:

Jim,

Can you explain a bit more? Do i need to create a new tab and enter the
formulas just as you noted? It's a bit out of my comfort level.

I added the first formula, and I get a dialog box for "update values?

Can you elaborate?



"Jim Thomlinson" wrote:

So you want to print let say 9 columns to a page. Assuming that to be correct
then there is nothing as direct as you would want. I would be inclined to on
a seperate tab just use formulas to transfer the data over.

in A1 =Sheet1!A1
in B1 =Sheet1!B1
in C1 =Sheet1!C1
in D1 =Sheet1!A14000
in E1 =Sheet1!B14000
in F1 =Sheet1!C14000
in G1 =Sheet1!A28000
in H1 =Sheet1!B28000
in I1 =Sheet1!C28000

and drag down. If this is a spreadsheet that you use a lot these formulas
will cause your spreadsheet to slow down as you will reach the calculation
limit of xl and any calculation will cuase a full workbook recalc. You might
want to delete the formulas after you print or switch calculation to manual.



--
HTH...

Jim Thomlinson


"jmcclain" wrote:

Sorry for the cryptic subject - wasn't sure how to explain.

I have a spreadsheet comprising (3) columns and 41,000+ lines.

I need to know if there is a easy way to reduce the page count vs cutting
and pasting additional columns to the right.

Any thoughts would be appreciated.

Jon

How to "merge" multiple colums to reduce page count
 

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 Fri, 19 Feb 2010 07:13:01 -0800, jmcclain
wrote:

Sorry for the cryptic subject - wasn't sure how to explain.

I have a spreadsheet comprising (3) columns and 41,000+ lines.

I need to know if there is a easy way to reduce the page count vs cutting
and pasting additional columns to the right.

Any thoughts would be appreciated.

Jon